Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time difference
This is time worksheet:
Time in/time out/Number hours/difference. First three are fine. The difference should be the difference between column C and 8 hours. Ideally, it would show the difference in hours and minutes. I've tried =C2-8, but all I get is #######. Any suggestions? -- Steph |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time difference
excel stores time in number of days, to 8 hours is really 8/24, or 1/3. Use
this formula =C2-8/24 or =C2-1/3 Other notes about time: -I like to keep it as 8/24, so it makes more sense to other people who might look at it, but it really doesn't matter which format you use. -If you want something in decimal hours (5.5 instead 5:30) mulitply the result by 24 and format as a number. -If you want elapsed time that will be over 24 hours use a custom number format of [h]:mm without the brackets, 27:34 would show like 3:34. "Stephanie" wrote: This is time worksheet: Time in/time out/Number hours/difference. First three are fine. The difference should be the difference between column C and 8 hours. Ideally, it would show the difference in hours and minutes. I've tried =C2-8, but all I get is #######. Any suggestions? -- Steph |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time difference
This is what I put, but it's not working.
=E2-8/24 h:mm =E2-1/3 h:mm doesn't work either. -- Steph "Sloth" wrote: excel stores time in number of days, to 8 hours is really 8/24, or 1/3. Use this formula =C2-8/24 or =C2-1/3 Other notes about time: -I like to keep it as 8/24, so it makes more sense to other people who might look at it, but it really doesn't matter which format you use. -If you want something in decimal hours (5.5 instead 5:30) mulitply the result by 24 and format as a number. -If you want elapsed time that will be over 24 hours use a custom number format of [h]:mm without the brackets, 27:34 would show like 3:34. "Stephanie" wrote: This is time worksheet: Time in/time out/Number hours/difference. First three are fine. The difference should be the difference between column C and 8 hours. Ideally, it would show the difference in hours and minutes. I've tried =C2-8, but all I get is #######. Any suggestions? -- Steph |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time difference
ok, I figured it out. It shows negative numbers as #####. Is there any way
to make it show as a negative? -- Steph "Sloth" wrote: excel stores time in number of days, to 8 hours is really 8/24, or 1/3. Use this formula =C2-8/24 or =C2-1/3 Other notes about time: -I like to keep it as 8/24, so it makes more sense to other people who might look at it, but it really doesn't matter which format you use. -If you want something in decimal hours (5.5 instead 5:30) mulitply the result by 24 and format as a number. -If you want elapsed time that will be over 24 hours use a custom number format of [h]:mm without the brackets, 27:34 would show like 3:34. "Stephanie" wrote: This is time worksheet: Time in/time out/Number hours/difference. First three are fine. The difference should be the difference between column C and 8 hours. Ideally, it would show the difference in hours and minutes. I've tried =C2-8, but all I get is #######. Any suggestions? -- Steph |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time difference
You could use an extra column, so that you have one column for positive, and
one for negative, but both showing positive. To do that, merely swop the arguments, as follows: =IF(C4<1/3,(8/24)-C4,"") to calculate negative hours and =IF(C41/3,C4-(8/24),"") to calculate positive hours. In this way, you can balance out overtime and time off. "Stephanie" wrote: ok, I figured it out. It shows negative numbers as #####. Is there any way to make it show as a negative? -- Steph "Sloth" wrote: excel stores time in number of days, to 8 hours is really 8/24, or 1/3. Use this formula =C2-8/24 or =C2-1/3 Other notes about time: -I like to keep it as 8/24, so it makes more sense to other people who might look at it, but it really doesn't matter which format you use. -If you want something in decimal hours (5.5 instead 5:30) mulitply the result by 24 and format as a number. -If you want elapsed time that will be over 24 hours use a custom number format of [h]:mm without the brackets, 27:34 would show like 3:34. "Stephanie" wrote: This is time worksheet: Time in/time out/Number hours/difference. First three are fine. The difference should be the difference between column C and 8 hours. Ideally, it would show the difference in hours and minutes. I've tried =C2-8, but all I get is #######. Any suggestions? -- Steph |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time difference
Excel can't handle negative dates or times. There are a couple ways you can
handle it... I. Typically people avoid negative time by using a formula like this =MAX(C1-8,0) this way if "Number of Hours" is below 8 then there is no overtime. II. If you need negative time then you can use a format like this h:mm;-#/24 or h:mm;-General the first output will round the number to the nearest hour (example: -3:35 will show as -4/24). The second will show the actuall number (example: -3:35 will show as -0.14931). Note, the value of the cell is not rounded in either, only the display is rounded (ie. the cell will show -4/24, but will equal -0.14931 if used in another cell's formula). III. If you need negative time in time format (for asthetic reasons) you can use this formula, but it can't be used in other formulas. =IF(C1<1/3,"-","")&TEXT(ABS(C1-1/3),"h:mm") IV. I am sure there are other options, but all of them will have some sort of drawback depending on what you want to do with the result. "Stephanie" wrote: ok, I figured it out. It shows negative numbers as #####. Is there any way to make it show as a negative? -- Steph "Sloth" wrote: excel stores time in number of days, to 8 hours is really 8/24, or 1/3. Use this formula =C2-8/24 or =C2-1/3 Other notes about time: -I like to keep it as 8/24, so it makes more sense to other people who might look at it, but it really doesn't matter which format you use. -If you want something in decimal hours (5.5 instead 5:30) mulitply the result by 24 and format as a number. -If you want elapsed time that will be over 24 hours use a custom number format of [h]:mm without the brackets, 27:34 would show like 3:34. "Stephanie" wrote: This is time worksheet: Time in/time out/Number hours/difference. First three are fine. The difference should be the difference between column C and 8 hours. Ideally, it would show the difference in hours and minutes. I've tried =C2-8, but all I get is #######. Any suggestions? -- Steph |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating time difference over midnight! | Excel Discussion (Misc queries) | |||
How can I use a time difference in a subsequent formula in Excel? | Excel Worksheet Functions | |||
Time Difference | Excel Worksheet Functions | |||
What is the formula for getting time difference e.g. ("4 hrs 15 m. | Charts and Charting in Excel | |||
Time / Formula to look at time difference | Excel Worksheet Functions |