Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Time differances
1 have 4 boxes
start time end time lunch hours worked 09:00 17:00 1.00 7:00 09:00 17:00 0.30 7:70 (should be 7:50) (B3-A3)*24-C3 How do I make it 9.5 hours by changing forumla Thanks |
#2
|
|||
|
|||
Trever,
=(B3-A3)*24-C3*24 Format for General, Number, or anything except Date/Time. Format - Cells - Number. You'll get 7.5. If you got 7:50, as you requested, it would look like hours and minutes. -- Earl Kiosterud www.smokeylake.com "Trever B" wrote in message ... 1 have 4 boxes start time end time lunch hours worked 09:00 17:00 1.00 7:00 09:00 17:00 0.30 7:70 (should be 7:50) (B3-A3)*24-C3 How do I make it 9.5 hours by changing forumla Thanks |
#3
|
|||
|
|||
Hi Trevor,
Time is entered using colons to separate minutes from hours, DO NOT USE A DECIMAL POINT where you should be using a colon. A value of 1.0 represents 24 hours not one hour.. A value of .3 represents 7 hours 12 minutes The formula to return decimal hours is:, formatted as 0.00 c2: =(b2-a2+(a2b2))-c)*24 and fill down.with the fill handle. If you format the result as time [h]:mm The logical expression (A2-b2) will return either 0 or 1, where a value of 1 represents 24 hours (1 day) and is necessary if hours go through midnight. The format of [h]:mm prevents hours from rolling off into days and not being shown, you will probably need that format for the total number of hours, if shown as an Excel time. For more information on Date and Time see http://www.mvps.org/dmcritchie/excel/datetime.htm --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Trever B" wrote in message ... 1 have 4 boxes start time end time lunch hours worked 09:00 17:00 1.00 7:00 09:00 17:00 0.30 7:70 (should be 7:50) (B3-A3)*24-C3 How do I make it 9.5 hours by changing forumla Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using VLOOKUP with a Date and Time | Excel Discussion (Misc queries) | |||
time math | Excel Worksheet Functions | |||
Time Sheets | New Users to Excel | |||
Time Sheet Calculation Help Needed! | Excel Worksheet Functions | |||
unmet challenge | Excel Worksheet Functions |