Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hours and minutes in excel
That's brilliant!
Many Thanks, Tone |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding hours and minutes | Excel Worksheet Functions | |||
how to calculate the hours and minutes in excel | Excel Discussion (Misc queries) | |||
hours & minutes ADDITION IN EXCEL | New Users to Excel | |||
adding rows of hours and minutes to get a total | Excel Worksheet Functions | |||
how to add hours and minutes in excel | New Users to Excel |