SOLVED

If date lands on a Friday, fall back to Thursday

Copper Contributor

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?

 

 

4 Replies
best response confirmed by NikolinoDE (Gold Contributor)
Solution

@JenniL0211 

 

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.)

@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. 

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

@JenniL0211 

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.

1 best response

Accepted Solutions
best response confirmed by NikolinoDE (Gold Contributor)
Solution

@JenniL0211 

 

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.)

View solution in original post