Multiple IF's

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
swjtx99Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
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 ExpertCommented:
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 DeveloperCommented:
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.
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

Saurabh Singh TeotiaCommented:
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...
Uptime Legal SystemsCommented:
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…")))
Excel amusantCommented:
Here is the formula,

=IF(G4>TODAY(),DAYS360(G4,TODAY()-1),DAYS360(G4,TODAY()))
swjtx99Author 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
Saurabh Singh TeotiaCommented:
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 DeveloperCommented:
You've changed the parameters of your question. You now want holidays accounted for which was not part of the original problem.
Saurabh Singh TeotiaCommented:
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 ExpertCommented:
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))
Saurabh Singh TeotiaCommented:
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...
Saurabh Singh TeotiaCommented:
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
Excel amusantCommented:
Here is the revised formula.

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


Also please see attached example.
ExampleformultipleIF.xlsx
Saurabh Singh TeotiaCommented:
Its fun to see so much participation here...
Saurabh Singh TeotiaCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Professor JMicrosoft Excel ExpertCommented:
Saurabh:-)
Excel amusantCommented:
Revised formula.


=IF(G7>TODAY(),NETWORKDAYS.INTL(G7,TODAY(),1,Holidays)+1,NETWORKDAYS.INTL(G7,TODAY(),1,Holidays)-1)
ExampleformultipleIF.xlsx
swjtx99Author 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
swjtx99Author Commented:
Thanks again,
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.