24 time format and calculations
 

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

and format as time

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

wrote in message
oups.com...
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?

24 time format and calculations
 

Try "hh:mm" in place of "00\:00"
Bob Phillips Wrote:
=TEXT(B1,"00\:00")-TEXT(A1,"00\:00")

and format as time

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

wrote in message
oups.com...
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 Miller