nested if statements

[suzyt1374]

I am trying to write a formula that will look at a date in column H2 and determine if that date is a prior week, week 1 or week 2

For example anything older than or equal to 8/19/17 is prior, any date between 8/20-8/26 is week 1, and any date between 8/27-9/2 is week 2.

I have it already done to isolate week 1 or week 2 but need to capture prior week. This is what I have to build off of, any help would be appreciated.

=IF(H2<=DATEVALUE("08/26/17"),"week 1","week 2")

there will be all different dates in column h2 ranging back a over a month or more in some cases.

 

[Scott T]

IF 8/27 - 9/2 is week 2 what about dates after 9/2?

 

[suzyt1374]

IF 8/27 - 9/2 is week 2 what about dates after 9/2?

There won't be I will change the date ranges each time for the next pay weeks so for this current pay cycle i need it through 9/2/17

 

[Scott T]

Try

=IF(H2<=DATEVALUE("8/19/17"),"pror week",IF(H2<=DATEVALUE("8/26/17"),"Week 1","Week 2"))

 

[suzyt1374]

thanks I was so close, appreciate the help, tested and working

 

[Scott T]

Glad to help.

 

[C with no eyes]

I am trying to write a formula that will look at a date in column H2 and determine if that date is a prior week, week 1 or week 2
_ _ _

there will be all different dates in column h2 ranging back a over a month or more in some cases.

I'm guessing you have a working spreadsheet which you will update and use moving forward. Changing the date every time is going to be a pain. Try this one:

<today()-6-weekday(today(),1),"prior week",if(and(h2=""><today()-6-weekday(today(),1),"prior week",if(and(h2="">IF(H2<today()-6-weekday(today(),1),"prior week",if(and(h2="">=TODAY()-6-WEEKDAY(TODAY(),1),H2<=TODAY()-WEEKDAY(TODAY(),1)),"Week 1",IF(AND(H2>=TODAY()+1-WEEKDAY(TODAY(),1),H2<=TODAY()+7-WEEKDAY(TODAY(),1)),"Week 2")))

I noticed your week runs from Sunday to Saturday so worked it around that. It will look at today's date and determine the week. You would never need to update the formula.
</today()-6-weekday(today(),1),"prior></today()-6-weekday(today(),1),"prior></today()-6-weekday(today(),1),"prior>

 

[C with no eyes]

That formula won't work. I can't figure out why it's not coming up properly.

 

[C with no eyes]

Nope, didn't work

 

[Joe4]

Does anyone have any idea why it will not display the formula correctly?

Yes, read this: https://www.mrexcel.com/forum/about-board/680917-formulas-not-appearing-completely-read.html