24 HOUR CLOCK MATH

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I HAVE A CELL WITH TWO TIMES IN IT. IE. 1900-2200, INDICATING THE OT AN INDIVIDUAL WORKED. IS THERE A FUNCTION TO DO THE MATH ON THAT FOR ME AND ENTER IT IN A SEPERATE CELL

THANKS FOR YOUR TIME AND HELP
STEVE
 
Hi Steve
first: please turn of your CAPS Lock: Difficult to read and considered
as shouting in NGs.

For your question: try (if A1 stores your value and you always enter
the time in the format HHMM-HHMM)
=TIME(--MID(A1,6,2),--RIGHT(A1,2),0) -
TIME(--LEFT(A1,2),--MID(A1,3,2),0)
format this resulting cell as 'Time'

This works only if the working time does not span midnight.

Note: I'd convert your current value into to cells (split the starting
time and ending time in two cell). Format thses cells as time (with a
colon to separate houers and minutes). With this you could use a simple
formula like
=B1-A1 (if A1 and B1 store your times)
or for times spanning midnight
=B1-A1+(B1<A1)
 
Back
Top