Hours and minutes in excel
 

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

Hours and minutes in excel
 

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

--
Regards

Roger Govier


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

Hours and minutes in excel
 

That's brilliant!

Many Thanks,

Tone

Hours and minutes in excel
 

ha scritto nel messaggio
oups.com...
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

Hours and minutes in excel
 

--
Ciao
Thunder

"Roger Govier" ha scritto nel messaggio
...
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

--
Regards

Roger Govier


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



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

Regards
Thunder

Hours and minutes in excel
 

Oops! False shipment :-P

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

Regards
Thunder


"Roger Govier" ha scritto nel messaggio
...
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

--
Regards

Roger Govier


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