Formula for IF it's the 2nd or 4th thursday of the month

[jmcole]

I thought the formula that Tony Valko gave me last night worked, but it didn't. It will work if you manually type in the date in J1, but I need that date to auto populate, =NOW().

The formulas in H15 and H16 are what I need help with. I have attached what I am working on.

[JohnTopley]

try

In H15

=IF((EOMONTH($J$1,-1)+1)+14-WEEKDAY((EOMONTH($J$1,-1)+1)+2),Data!C30,"")

in H16

=IF((EOMONTH($J$1,-1)+1)+28-WEEKDAY((EOMONTH($J$1,-1)+1)+2),Data!C30,"")

OR

=IF(EOMONTH($J$1,-1)+15-WEEKDAY(EOMONTH($J$1,-1)+3),Data!C30,"")

=IF(EOMONTH($J$1,-1)+29-WEEKDAY(EOMONTH($J$1,-1)+3),Data!C30,"")

The first formulae were just to reminder that the calculation started from 1st of a month

[jmcole]

try

In H15

=IF((EOMONTH($J$1,-1)+1)+14-WEEKDAY((EOMONTH($J$1,-1)+1)+2),Data!C30,"")

in H16

=IF((EOMONTH($J$1,-1)+1)+28-WEEKDAY((EOMONTH($J$1,-1)+1)+2),Data!C30,"")

OR

=IF(EOMONTH($J$1,-1)+15-WEEKDAY(EOMONTH($J$1,-1)+3),Data!C30,"")

=IF(EOMONTH($J$1,-1)+29-WEEKDAY(EOMONTH($J$1,-1)+3),Data!C30,"")

The first formulae were just to reminder that the calculation started from 1st of a month

Doesn't seem to work John. They both populate no matter what date populates.

[Jonmo1]

Try

=WORKDAY.INTL(INT(J1)-DAY(J1),2,"1110111")
Change the 2 to 4 to get the 4th thursday.


If you can change J1 to =TODAY() instead of =NOW(), then
=WORKDAY.INTL(J1-DAY(J1),2,"1110111")

[jmcole]

Try

=WORKDAY.INTL(INT(J1)-DAY(J1),2,"1110111")
Change the 2 to 4 to get the 4th thursday.


If you can change J1 to =TODAY() instead of =NOW(), then
=WORKDAY.INTL(J1-DAY(J1),2,"1110111")

Tried this with the =NOW(), but it populates no matter what day I change the computer to:

=IF(WORKDAY.INTL(INT(J1)-DAY(J1),2,"1110111"),Data!C30,"")

[JohnTopley]

TRy

=IF(INT(J1)=EOMONTH($J$1,-1)+15-WEEKDAY(EOMONTH($J$1,-1)+3),Data!C30,"")

=IF(INT(J1)=EOMONTH($J$1,-1)+29-WEEKDAY(EOMONTH($J$1,-1)+3),Data!C31,"")

[jmcole]

TRy

=IF(INT(J1)=EOMONTH($J$1,-1)+15-WEEKDAY(EOMONTH($J$1,-1)+3),Data!C30,"")

=IF(INT(J1)=EOMONTH($J$1,-1)+29-WEEKDAY(EOMONTH($J$1,-1)+3),Data!C31,"")

That's it!!
Thank you very much!!!

[Jonmo1]

Tried this with the =NOW(), but it populates no matter what day I change the computer to:
=IF(WORKDAY.INTL(INT(J1)-DAY(J1),2,"1110111"),Data!C30,"")
Tried this with the =NOW(), but it populates no matter what day I change the computer to:

You forgot the 2nd half of the if expression...
That is basically saying IF Workday Then...
You need to say IF Workday equals something Then...

=IF(WORKDAY.INTL(INT(J1)-DAY(J1),2,"1110111")=INT(J1),Data!C30,"")

[Tony Valko]

I didn't download your file.

If the cell contains the NOW() function then it returns BOTH the date and time.

Do you actually need the time returned?

If not, then you can replace NOW() with TODAY() which returns the date only. Then the formula I suggested will return the correct result.

[jmcole]

I didn't download your file.

If the cell contains the NOW() function then it returns BOTH the date and time.

Do you actually need the time returned?

If not, then you can replace NOW() with TODAY() which returns the date only. Then the formula I suggested will return the correct result.

You are correct sir, thank you. I've been using NOW() forever, I guess it's time to change.

[Jonmo1]

I guess it's time to change.

Not really time to change.
Just recognize the difference between the two functions, and use the one that is appropriate to each situation.

If you need the time value included with the date, use NOW.
If not, use Today.

[Tony Valko]

Good deal. Thanks for the feedback!