Need formula to calculate days between dates based on multiple if statements

[JLAJAF]

I all of these scenarios combined into one formula please.

ScenarioDesired ResultAnswerTerm/Transfer DateNew Hire Date# Workdays Open
1Transfer date before June 30, new hire date blankCalc # of days between Jul 1 and today
2Transfer date before June 30, new hire date on or after July 1Calc # of days between Jul 1 and today
3Transfer date after June 30, new hire date blank Calc # of days between July 1 and today
4Transfer date after June 30, new hire date not blankCalc # of days between transfer date and new hire date
5Transfer date before June 30, new hire date before July 1

More detail in attachment - can't really tell what's going on here without seeing the remainder of the info.

 

[Hui]

Jlajaf

Firstly, Welcome to the Chandoo.org Forums

Your post has been cross-posted, which means it has been seen on other websites. This is considered poor practice, as it can waste peoples time, which could be spent elsewhere, especially if you get a solution and don't notify us.

I encourage you to please read the site rules at:
http://forum.chandoo.org/link-forums/new-users-please-read.17/

 

[JLAJAF]

https://www.excelforum.com/excel-fo...-help-with-multiple-if-statement-formula.html

Thank you for clarifying

 

[NARAYANK991]

Hi ,

It is not clear whether the comma separating the conditions signifies an AND or an OR.

Suppose we take this :

Transfer date before June 30, new hire date blank

Does it mean :

Transfer date before June 30 AND new hire date blank

or does it mean :

Transfer date before June 30 OR new hire date blank

Narayan

 

[JLAJAF]

Hi ,

It is not clear whether the comma separating the conditions signifies an AND or an OR.

Suppose we take this :

Transfer date before June 30, new hire date blank

Does it mean :

Transfer date before June 30 AND new hire date blank

or does it mean :

Transfer date before June 30 OR new hire date blank

Narayan

It is and rather than Or. Thanks!

 

[JLAJAF]

It is and rather than Or. Thanks!

Ignore my formulas in column N please.

 

[NARAYANK991]

Hi ,

Can we describe the possibilities in more detail.

We have the following :

1. A - Transfer date on or before June 30.

2. not A - Transfer date after June 30.

3. B - New hire date blank.

4. not B - New hire date not blank.

5. C - New hire date on or before June 30.

6. not C - New hire date after June 30.

If we combine them , the possibilities are :

a. If A , if B.

b. If A , if not B , if C.

c. If A , if not B , if not C.

d. If not A , if B.

e. If not A , if not B , if C.

f. If not A , if not B , if not C.

Can you specify the outputs for each of the conditions a through f ?

From what you have written , conditions a and d have the same output viz. calculate the number of days between July 1 and today. Is this correct ?

If so , there is no need to test for If A or If not A ; we just test for whether the new hire date is blank.

Next , if the new hire date is not blank , we have the 4 possibilities :

b. New hire date is on or before June 30 , and the transfer date is on or before June 30

c. New hire date is after June 30 , and the transfer date is on or before June 30

e. New hire date is on or before June 30 , and the transfer date is after June 30

f. New hire date is after June 30 , and the transfer date is after June 30

From what you have posted ,

b results in an output of 0 (zero).

c results in the difference between New hire date and July 1.

Can you clarify about e and f ?

Narayan

 

[JLAJAF]

I was out of the office yesterday and am sorry I didn't get back to you. My manager changed what she was looking for on this so I no longer need to do this calculation. I very much appreciate your help. Thanks!