Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF/Nesting When Calculation Involves a Date
Any Nested IF help is gratefully received and acknowledged . . .
I have a calculation I need to perform for number of days to close a case that is calculating based on subtracting one cell from another: January 1, 2008 - January 15, 2008 = 14 days There are three possible outcomes: Case has an end date and it just needs to calculate the difference between the two cells: =SUM(R4-E4) Case opened and closed on the same day (so it shows up as 0, but really needs to be a 1. The forumula below is working for this outcome): =IF(R4-E4=0,1,R4-E4) Case is still open (no end date entered) -- this one shows up on my worksheet as 39,472 using the same formula above -- I cannot figure out how to have it just show "Open" How can I put together a formula that provides the correct answer based on all three conditions: Open No end date entered yet 1 Number of Days to Close Case = 0 Perform the calculation between open and closed dates |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF/Nesting When Calculation Involves a Date
=IF(R4-E4=0,1,IF(R4<"",R4-E4,"Open"))
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Annie" wrote in message ... Any Nested IF help is gratefully received and acknowledged . . . I have a calculation I need to perform for number of days to close a case that is calculating based on subtracting one cell from another: January 1, 2008 - January 15, 2008 = 14 days There are three possible outcomes: Case has an end date and it just needs to calculate the difference between the two cells: =SUM(R4-E4) Case opened and closed on the same day (so it shows up as 0, but really needs to be a 1. The forumula below is working for this outcome): =IF(R4-E4=0,1,R4-E4) Case is still open (no end date entered) -- this one shows up on my worksheet as 39,472 using the same formula above -- I cannot figure out how to have it just show "Open" How can I put together a formula that provides the correct answer based on all three conditions: Open No end date entered yet 1 Number of Days to Close Case = 0 Perform the calculation between open and closed dates |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF/Nesting When Calculation Involves a Date
Bob, you are my HERO! Truly, thank so very much. I'm going to have to
figure out what all this means so I can replicate it in the future. Again, THANKS! Ann "Bob Phillips" wrote: =IF(R4-E4=0,1,IF(R4<"",R4-E4,"Open")) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Annie" wrote in message ... Any Nested IF help is gratefully received and acknowledged . . . I have a calculation I need to perform for number of days to close a case that is calculating based on subtracting one cell from another: January 1, 2008 - January 15, 2008 = 14 days There are three possible outcomes: Case has an end date and it just needs to calculate the difference between the two cells: =SUM(R4-E4) Case opened and closed on the same day (so it shows up as 0, but really needs to be a 1. The forumula below is working for this outcome): =IF(R4-E4=0,1,R4-E4) Case is still open (no end date entered) -- this one shows up on my worksheet as 39,472 using the same formula above -- I cannot figure out how to have it just show "Open" How can I put together a formula that provides the correct answer based on all three conditions: Open No end date entered yet 1 Number of Days to Close Case = 0 Perform the calculation between open and closed dates |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF/Nesting When Calculation Involves a Date
H Annie,
When you leave out the end date, you will get -39472, not 39472. Therefore you just need another if statement to test for an answer less than zero. =IF(R4-E4<0,0,IF(R4-E4=0,1,R4-E4)) Regards - Dave. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Please help me! making table involves percents | Excel Discussion (Misc queries) | |||
End Date Calculation (adding a start date duration) | Excel Discussion (Misc queries) | |||
Tricky Date calculation: How to calculate a future date | Excel Discussion (Misc queries) | |||
Date Calculation | Excel Discussion (Misc queries) | |||
Date calculation | New Users to Excel |