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

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
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top