Hours and minutes in excel

[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

 

[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

 

[worzell]

That's brilliant!

Many Thanks,

Tone

 

[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

In cell D11 you can insert:

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

Regards

Thunder

 

[Thunder]

--
Ciao
Thunder

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

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

Regards
Thunder

 

[Thunder]

Oops! False shipment

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

Regards
Thunder