24 time format and calculations

[stevewy]

I have a spreadsheet with start and end times for people's working day,
in 24 hour format. So it will be 830, 900, 1030, 715 etc in the start
column and 1700, 1715, 1550 etc in the end column. I would like to do
a calculation on these times to work out for each person how many hours
are in their working day (including lunch and breaks). At the moment
Excel regards these as regular numbers, so I cannot do a calculation
like 1700-830 because it won't come out right. I have tried fiddling
with the cell format with no success. How do I do it?

 

[Bob Phillips]

=TEXT(B1,"00\:00")-TEXT(A1,"00\:00")

and format as time

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Harlan Grove]

(e-mail address removed) wrote...

I have a spreadsheet with start and end times for people's working day,
in 24 hour format. So it will be 830, 900, 1030, 715 etc in the start
column and 1700, 1715, 1550 etc in the end column. I would like to do
a calculation on these times to work out for each person how many hours
are in their working day (including lunch and breaks). At the moment
Excel regards these as regular numbers, so I cannot do a calculation
like 1700-830 because it won't come out right. I have tried fiddling
with the cell format with no success. How do I do it?

First, cell format has **NOTHING** to do with cell contents or values.
Formulas *ONLY* use the values of other cells. So no formatting you
apply in any cell would affect formulas in other cells except for
formulas involving CELL("Format",.).

You have to change the values in formulas in other cells. If start time
were in B3 and end time in C3, both as numbers in 0000 format, then use
either of the following:

=TEXT(TEXT(C3,"00\:00")-TEXT(B3,"00\:00"),"[h]mm")

which returns a text result also in 24 hour format, or

=ROUNDDOWN(C3-B3,-2)+MOD(MOD(C3,100)-MOD(B3,100),60)

which returns a numeric result also in 24 hour format.