| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with If statement to subtract time (follow up)
Hi all
I am trying to subtract 24 hours from a time if it is actually over 24 hours, if not then show 0 my formula works for everthing over 24 hours but can't get it show a 0 or under 24 hours. In A1 8/3/2008 7:21 in B1 8/6/2008 15:36 in C1 I have =if(("B1-A1")(A1+"1"),(B1-A1)-"1",0) The answer is 56:15:00 which is what I want but if B1 is 8/3/2008 it is showing ##### indicating a negative time number instead of 0 any help would be appreciated I did receive two responses but both options didn't work as expected. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with If statement to subtract time (follow up)
Try
=IF(B1-A11,B1-A1,0) format as [h]:mm:ss -- Regards, Peo Sjoblom "richard.littlewing" <richard.littlewing@gmail wrote in message ... Hi all I am trying to subtract 24 hours from a time if it is actually over 24 hours, if not then show 0 my formula works for everthing over 24 hours but can't get it show a 0 or under 24 hours. In A1 8/3/2008 7:21 in B1 8/6/2008 15:36 in C1 I have =if(("B1-A1")(A1+"1"),(B1-A1)-"1",0) The answer is 56:15:00 which is what I want but if B1 is 8/3/2008 it is showing ##### indicating a negative time number instead of 0 any help would be appreciated I did receive two responses but both options didn't work as expected. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with If statement to subtract time (follow up)
If you want 56:15
=IF(B1-A11,B1-A1-1,0) format as [h]:mm:ss -- Regards, Peo Sjoblom "Peo Sjoblom" wrote in message ... Try =IF(B1-A11,B1-A1,0) format as [h]:mm:ss -- Regards, Peo Sjoblom "richard.littlewing" <richard.littlewing@gmail wrote in message ... Hi all I am trying to subtract 24 hours from a time if it is actually over 24 hours, if not then show 0 my formula works for everthing over 24 hours but can't get it show a 0 or under 24 hours. In A1 8/3/2008 7:21 in B1 8/6/2008 15:36 in C1 I have =if(("B1-A1")(A1+"1"),(B1-A1)-"1",0) The answer is 56:15:00 which is what I want but if B1 is 8/3/2008 it is showing ##### indicating a negative time number instead of 0 any help would be appreciated I did receive two responses but both options didn't work as expected. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with If statement to subtract time (follow up)
Why is it 56:15 not 80:15?
-- __________________________________ HTH Bob "richard.littlewing" <richard.littlewing@gmail wrote in message ... Hi all I am trying to subtract 24 hours from a time if it is actually over 24 hours, if not then show 0 my formula works for everthing over 24 hours but can't get it show a 0 or under 24 hours. In A1 8/3/2008 7:21 in B1 8/6/2008 15:36 in C1 I have =if(("B1-A1")(A1+"1"),(B1-A1)-"1",0) The answer is 56:15:00 which is what I want but if B1 is 8/3/2008 it is showing ##### indicating a negative time number instead of 0 any help would be appreciated I did receive two responses but both options didn't work as expected. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with If statement to subtract time (follow up)
Try this:
=MAX(0,(B1-A1)-"24:00") format cell as [h]:mm:ss "richard.littlewing" wrote: Hi all I am trying to subtract 24 hours from a time if it is actually over 24 hours, if not then show 0 my formula works for everthing over 24 hours but can't get it show a 0 or under 24 hours. In A1 8/3/2008 7:21 in B1 8/6/2008 15:36 in C1 I have =if(("B1-A1")(A1+"1"),(B1-A1)-"1",0) The answer is 56:15:00 which is what I want but if B1 is 8/3/2008 it is showing ##### indicating a negative time number instead of 0 any help would be appreciated I did receive two responses but both options didn't work as expected. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with If statement to subtract time (follow up)
Slightly shorter...
=MAX(0,B1-A1-1) "richard.littlewing" wrote: Hi all I am trying to subtract 24 hours from a time if it is actually over 24 hours, if not then show 0 my formula works for everthing over 24 hours but can't get it show a 0 or under 24 hours. In A1 8/3/2008 7:21 in B1 8/6/2008 15:36 in C1 I have =if(("B1-A1")(A1+"1"),(B1-A1)-"1",0) The answer is 56:15:00 which is what I want but if B1 is 8/3/2008 it is showing ##### indicating a negative time number instead of 0 any help would be appreciated I did receive two responses but both options didn't work as expected. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| using if statement to subtract 24 hours from time still shows as a negative time from both responses under 24 hours | Excel Worksheet Functions | |||
| using if statement to subtract 24 hours from time | Excel Worksheet Functions | |||
| Subtract hours from a time and get the correct time | Excel Worksheet Functions | |||
| how to increment time & subtract time? | Excel Worksheet Functions | |||
| how do I add/subtract time when I'm going from PM to AM (ex. 11:4. | Excel Worksheet Functions | |||
Hybrid Mode
