Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
end - start less one hour for lunch
I have gotten some great tips from this forum and have been to the site
http://www.mvps.org/dmcritchie/excel/datetime.htm which was very helpful. But - can someone give me the quick and painless way to calculate the difference in time less one hour (lunch). I am currently using =E9-C9+(C9E9) and formatted it to [h]:mm:ss , then having a cell next door to it to calculate it into decimal hours (F9*24-1) really... but how do I get that (-1 hour) into the cell that calculates the hours/minutes? I have tried tagging a -1:00 on the end or just a -1 but I get the ole #VALUE error. Thanks in advance - I know this is likely easy... Michelle |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
end - start less one hour for lunch
"mochamichelle" wrote in message
... I have gotten some great tips from this forum and have been to the site http://www.mvps.org/dmcritchie/excel/datetime.htm which was very helpful. But - can someone give me the quick and painless way to calculate the difference in time less one hour (lunch). I am currently using =E9-C9+(C9E9) and formatted it to [h]:mm:ss , then having a cell next door to it to calculate it into decimal hours (F9*24-1) really... but how do I get that (-1 hour) into the cell that calculates the hours/minutes? I have tried tagging a -1:00 on the end or just a -1 but I get the ole #VALUE error. =E9-C9+(C9E9)-TIME(1,0,0) or =E9-C9+(C9E9)-1/24 -- David Biddulph |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
end - start less one hour for lunch
Michelle,
try this: =E9-C9-1/24+(C9E9) as these are times, which are stored as fractions of a 24-hour day, you need to subtract 1/24th for an hour. Your other formula would now become: =F9*24 Hope this helps. Pete mochamichelle wrote: I have gotten some great tips from this forum and have been to the site http://www.mvps.org/dmcritchie/excel/datetime.htm which was very helpful. But - can someone give me the quick and painless way to calculate the difference in time less one hour (lunch). I am currently using =E9-C9+(C9E9) and formatted it to [h]:mm:ss , then having a cell next door to it to calculate it into decimal hours (F9*24-1) really... but how do I get that (-1 hour) into the cell that calculates the hours/minutes? I have tried tagging a -1:00 on the end or just a -1 but I get the ole #VALUE error. Thanks in advance - I know this is likely easy... Michelle |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
end - start less one hour for lunch
Okay - scrap that - I figured that out
-TIME(1,0,0) YAY!! "mochamichelle" wrote: I have gotten some great tips from this forum and have been to the site http://www.mvps.org/dmcritchie/excel/datetime.htm which was very helpful. But - can someone give me the quick and painless way to calculate the difference in time less one hour (lunch). I am currently using =E9-C9+(C9E9) and formatted it to [h]:mm:ss , then having a cell next door to it to calculate it into decimal hours (F9*24-1) really... but how do I get that (-1 hour) into the cell that calculates the hours/minutes? I have tried tagging a -1:00 on the end or just a -1 but I get the ole #VALUE error. Thanks in advance - I know this is likely easy... Michelle |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
end - start less one hour for lunch
Thank you so much for your reply
Now - I have an issue - if there is no time entered, my Duration cells are a negative number! (and show as ########) is there a way to say this number must be a positive number or else it is zero Thanks! Michelle "David Biddulph" wrote: "mochamichelle" wrote in message ... I have gotten some great tips from this forum and have been to the site http://www.mvps.org/dmcritchie/excel/datetime.htm which was very helpful. But - can someone give me the quick and painless way to calculate the difference in time less one hour (lunch). I am currently using =E9-C9+(C9E9) and formatted it to [h]:mm:ss , then having a cell next door to it to calculate it into decimal hours (F9*24-1) really... but how do I get that (-1 hour) into the cell that calculates the hours/minutes? I have tried tagging a -1:00 on the end or just a -1 but I get the ole #VALUE error. =E9-C9+(C9E9)-TIME(1,0,0) or =E9-C9+(C9E9)-1/24 -- David Biddulph |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
end - start less one hour for lunch
"mochamichelle" wrote in message
... "David Biddulph" wrote: "mochamichelle" wrote in message ... I have gotten some great tips from this forum and have been to the site http://www.mvps.org/dmcritchie/excel/datetime.htm which was very helpful. But - can someone give me the quick and painless way to calculate the difference in time less one hour (lunch). I am currently using =E9-C9+(C9E9) and formatted it to [h]:mm:ss , then having a cell next door to it to calculate it into decimal hours (F9*24-1) really... but how do I get that (-1 hour) into the cell that calculates the hours/minutes? I have tried tagging a -1:00 on the end or just a -1 but I get the ole #VALUE error. =E9-C9+(C9E9)-TIME(1,0,0) or =E9-C9+(C9E9)-1/24 Thank you so much for your reply Now - I have an issue - if there is no time entered, my Duration cells are a negative number! (and show as ########) is there a way to say this number must be a positive number or else it is zero If you want to show negative times, you could use Tools/ Options/ Calculation, and select 1904 date system, but otherwise if you want to limit to a minimum of zero you can use =MAX(0,E9-C9+(C9E9)-1/24) or if you want to leave the output blank if either of the inputs is empty, you could use =IF(OR(ISBLANK(C9),ISBLANK(E9)),"",E9-C9+(C9E9)-1/24) -- David Biddulph |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
random start and calculation | Excel Worksheet Functions | |||
count between start date and end date | Excel Discussion (Misc queries) | |||
OT :Start your own online business today !start making dollars | Excel Discussion (Misc queries) | |||
Convert decimal hour into time format? | Excel Discussion (Misc queries) | |||
Help! Excel 2000 fully SP'ed will not start | Excel Discussion (Misc queries) |