Multiple IF's

swjtx99
swjtx99 used Ask the Experts™
on
Hi,

I have a formula: =IF(INT(G139)=INT(TODAY()),0,NETWORKDAYS(G139,TODAY(),Holidays))

This does not work for me. If the date is yesterday, it says 2 if it's tomorrow, it returns -2.

What I need is:
If the date is today = 0
If the date is yesterday = 1
if the date is the day before yesterday = 2
ect.
if the date is tomorrow = -1
if the date is the day after tomorrow = -2
Ect.

Thanks in advance,

swjtx99
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2015

Commented:
swjtx99,

I'm confused here what you are trying to do because your comparison formula at the first place which is...

INT(Today())

Don't do anything it's same as today so not sure why you have int their..

Can you help me understand what you want to do by this formula and i can write one for you...

Saurabh...
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
assuming your date is in A1 then use this formula

=IF(A1=TODAY(),0,IF(A1=TODAY()-1,1,IF(A1=TODAY()-2,2,IF(A1=TODAY()+1,-1,IF(A1=TODAY()+2,-2,"")))))
Russ SuterSenior Software Developer

Commented:
If you're just looking for the day offset you can do it without any IF statements at all.
=DAYS360(A2, TODAY())

Open in new window

Of course, A2 is just a cell with a date value in it. Substitute A2 for the cell of your choice.
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

Top Expert 2015

Commented:
And what i understand if you just want to subtract the dates which you have with today's date..if you simply do this...

=Today()-G139

Now if the date is a future date it will give you - and if it has passed it will give you a positive value what you are looking for..

Saurabh...
Try this.  I've changed it to add 1 or subtract 1 depending on whether the date is ahead or behind.

=IF(INT(G139)=INT(TODAY()),0,IF(INT(G139)<INT(TODAY()),NETWORKDAYS(G139,TODAY())-1,IF(INT(G139)>INT(TODAY()),NETWORKDAYS(G139,TODAY())+1,"Uh…")))
Here is the formula,

=IF(G4>TODAY(),DAYS360(G4,TODAY()-1),DAYS360(G4,TODAY()))

Author

Commented:
Hi,

I tried all the examples provided by all responders and none worked for what I need.

I'll provide an example. In the highlighted column is the data that I want to appear as a result of the formula.

Thanks in advance,

swjtx99
ExampleformultipleIF.xlsx
Top Expert 2015

Commented:
Their you go i believe this is what you wanted...

=IF(G2<TODAY(),NETWORKDAYS(G2,TODAY(),Holidays!$A$2:$A$19)-1,-NETWORKDAYS(TODAY(),G2,Holidays!$A$2:$A$19))

Open in new window


Enclosed is your workbook...

Saurabh...
ExampleformultipleIF.xlsx
Russ SuterSenior Software Developer

Commented:
You've changed the parameters of your question. You now want holidays accounted for which was not part of the original problem.
Top Expert 2015

Commented:
Small tweak as i was sitting on 30th dec...

=IF(G2<TODAY(),NETWORKDAYS(G2,TODAY(),Holidays!$A$2:$A$19)-1,-NETWORKDAYS(TODAY(),G2,Holidays!$A$2:$A$19)+1)

Open in new window


Your workbook...

Saurabh...
ExampleformultipleIF.xlsx
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
please use this formula

=IF(NETWORKDAYS.INTL(G2,TODAY()-2,1,Holidays!$A$2:$A$19)<0,NETWORKDAYS.INTL(G2,TODAY()-2,1,Holidays!$A$2:$A$19)+2,NETWORKDAYS.INTL(G2,TODAY()-2,1,Holidays!$A$2:$A$19))
Top Expert 2015

Commented:
Jim,

Beat you to it.. :-)

Russ Suter

He hasn't changed the original requirements if you see the question the formula he posted it included holidays in it and lately when i understood what he is trying to do i posted my formula which wasn't just accounting for Holidays..So if you account for holidays that formula i posted works..

Saurabh...
Top Expert 2015

Commented:
Time to hit Bed..one last amendment in the formula because of date confusion..This part of the formula which is..

G2<TODAY()

Will be

G2<=TODAY()

So therefore the formula will become...

=IF(G2<=TODAY(),NETWORKDAYS(G2,TODAY(),Holidays!$A$2:$A$19)-1,-NETWORKDAYS(TODAY(),G2,Holidays!$A$2:$A$19)+1)

Open in new window


Again your workbook for reference..

Saurabh...
ExampleformultipleIF.xlsx
Here is the revised formula.

=IF(G2<TODAY(),NETWORKDAYS(G2,TODAY(),Holidays)-1,NETWORKDAYS(G2,TODAY(),Holidays))


Also please see attached example.
ExampleformultipleIF.xlsx
Top Expert 2015

Commented:
Its fun to see so much participation here...
Top Expert 2015
Commented:
Best formula that i can come so far...

=NETWORKDAYS(G2,TODAY(),Holidays)+IF(G2<=TODAY(),-1,1)

Open in new window


Workbook for reference...

Saurabh...
ExampleformultipleIF.xlsx
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
Saurabh:-)
Revised formula.


=IF(G7>TODAY(),NETWORKDAYS.INTL(G7,TODAY(),1,Holidays)+1,NETWORKDAYS.INTL(G7,TODAY(),1,Holidays)-1)
ExampleformultipleIF.xlsx

Author

Commented:
Hi,

Sheet attached with everyone's contribution in the order posted. Looks like Saurabh Singh Teotia's solution was the first that worked but I like the last one he posted for the reduced size.

Starting from 0 (today), I marked red at first error both positive and negative.

Thanks everyone,

swjtx99
ExampleformultipleIF2.xlsx

Author

Commented:
Thanks again,

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial