Forum Discussion
Solved
If date lands on a Friday, fall back to Thursday
Hi all! I'm trying to figure out what to add to my formula so that if my date falls on a Friday, it will back date it to Thursday.
This is the string of formulas I have thus far:
My date is in Cell B97
=WORKDAY.INTL(B97,8,1,$A$59:$A$87)
=WORKDAY(C169,0,#REF!)
=+D169 (puts date as a day)
=D169
=ISNA(F169)
=IF(G169=FALSE, F169,C169)
=+H169 (puts day as a date)
Would I just need to add in some type of =IF formula to the first cell with a formula?
How about this formula:
=IF(WEEKDAY(B97)=6,B97-1,B97)
(You can't actually backdate B97 itself, but you can in some nearby column easily produce the date of the Thursday immediately before it, if B97 is a Friday.)
How about this formula:
=IF(WEEKDAY(B97)=6,B97-1,B97)
(You can't actually backdate B97 itself, but you can in some nearby column easily produce the date of the Thursday immediately before it, if B97 is a Friday.)
- Cancel my first response. I think I’ll be able to figure it out using your sample! I appreciate the help! I am in no way an expert at Excel. I do what I can to get by with a lot of searches and piecing things together lol
I think I’ll be able to figure it out using your sample! I appreciate the help! I am in no way an expert at Excel. I do what I can to get by with a lot of searches and piecing things together lol
I would hope you'll figure it out, since the single formula I gave you did exactly what you were asking, and what you'd been attempting to do through a long series of formulas. (My parenthetical "PS" was just in case I'd mis-interpreted your request...not the main point.)
Here's a resource that you might find helpful in your searching. That link will take you to a set of Date and Time functions, but take the time to poke around in the complete site. You'll find it useful.
It's commendable that you do as much as you can on your own; that's far and away the best way to learn Excel.
mathetes thank you. I don’t want the date in B97 to back date, I want the resulting date from the rest of the formula chain to backdate if it falls on a Friday.
