Calculate closest Friday

[Hoss97]

I am trying to modify the formula listed below to return the date of the closest Friday. So if the date falls on a wednesday, thursday or friday, it returns that friday. Then if a date falls on the tuesday or monday, it would return the previous friday.

=D175-WEEKDAY(D175,3)-IF(AND(WEEKDAY(D175,3)>=3,WEEKDAY(D175,2)<=5),5,3)

 

[jasonb75]

Try

Regular date system

=MROUND(D175,7)-1

1904 date system

=MROUND(D175,7)

Or compatible with both

=MROUND(D175,7)-(YEAR(1)=1900)

 

[rdw72777]

See if this works. Note the "+6" that I use in the formula isn't a random constant, its the value of a Friday in the Workday function, and the "-1" at the end of the formula also isn't random, its the number of days back into the prior week to go to find Friday (i.e. Saturday is day 7, friday is day 6, so 7-6 is 1).

=IF(WEEKDAY(D175,1)>2,D175+6-WEEKDAY(D175,1),D175-WEEKDAY(D175,1)-1)

 

[barry houdini]

=MROUND(D175,7)-(YEAR(1)=1900)

Doesn't always work for me, Jason. If D175 is a Tuesday and I have 1904 date system that gives me the following Friday rather than the previous one. This one should work in any case:

=D175+3-WEEKDAY(D175-3)

 

[jasonb75]

Doesn't always work for me, Jason. If D175 is a Tuesday and I have 1904 date system that gives me the following Friday rather than the previous one. This one should work in any case:

=D175+3-WEEKDAY(D175-3)

Oops, I missed that one.

 

[jasonb75]

Far less elegant than your suggestion Barry, but I was sure I could make it work somehow

=MROUND(D175-(YEAR(1)<>1900),7)-(YEAR(1)=1900)

Think I got it right this time

 

[Hoss97]

=MROUND(D175-(YEAR(1)<>1900),7)-(YEAR(1)=1900)

This is the formula the I used, and it worked perfectly!! Thank you very much for the help. I will also try the other formula that was posted, and will let you the results from that one as well. I'll have to research to understand this, but thank you again!!