Hours and minutes in excel

W

worzell

Hello,

I am trying to create a timesheet spreadsheet and have come upon a
problem.

I have four cells containing start time(D6), lunch out(D7), lunch
in(D8) and finish time(D9).

EG:

08:30 AM (start)
01:00 PM (lunch out)
02:00 PM (lunch in)
06:00 PM (finish)
08:30 (total)

The total in D10 returns the total time as 08:30 using the formula
=(D7<D6)+D7-D6+(D9<D8)+D9-D8

I need this return as 8.5 hours but have searched high and low and can
find nothing to make it work!!

Any help would be greatly appreciated.

Cheers,

Tone
 
R

Roger Govier

Hi

Excel stores times as fractions of a day. Multiply your whole formula by
24 and format the cell as General and you will get 8.5
=((D7<D6)+D7-D6+(D9<D8)+D9-D8)*24
 
T

Thunder

Hello,

I am trying to create a timesheet spreadsheet and have come upon a
problem.

I have four cells containing start time(D6), lunch out(D7), lunch
in(D8) and finish time(D9).

EG:

08:30 AM (start)
01:00 PM (lunch out)
02:00 PM (lunch in)
06:00 PM (finish)
08:30 (total)

The total in D10 returns the total time as 08:30 using the formula
=(D7<D6)+D7-D6+(D9<D8)+D9-D8

I need this return as 8.5 hours but have searched high and low and can
find nothing to make it work!!

Any help would be greatly appreciated.

Cheers,

Tone
Click to expand...

In cell D11 you can insert:

=(HOUR(D10)*60+MINUTE(D10))/60

Regards

Thunder
 
T

Thunder

--
Ciao
Thunder

Roger Govier said:
Hi

Excel stores times as fractions of a day. Multiply your whole formula by
24 and format the cell as General and you will get 8.5
=((D7<D6)+D7-D6+(D9<D8)+D9-D8)*24
Click to expand...
Sure the solution of Roger is the best one. Mine is alone an alternative.

Regards
Thunder
 
T

Thunder

Oops! False shipment :p

Sure the solution of Roger is the best one. Mine is alone an alternative.

Regards
Thunder
 
Top