Transforming weekend date to previous Friday

[RaydenUK]

Is there a code or formula that would convert a weekend date to the previous friday at 11:59pm? For example: in cell A1 I have "5/23/15 8:37", which is a saturday. How could I have that number transformed into "5/22/15 23:59", which is the friday prior to that saturday? I have several other dates in my list that do not end on the weekend and those need to stay the same. Any help would be greatly appreciated.

 

[Jonmo1]

Try

=WORKDAY(A1+1,-1)+MOD(A1,1)

 

[SpillerBD]

You'll need WEEKDAY(date) to retrieve the condition to calculate on. There are several modes.
=IF(OR(WEEKDAY(B9,2)=6,WEEKDAY(B9,2)=7),(DATE(YEAR(B9),MONTH(B9),DAY(B9)+5-WEEKDAY(B9,2)))+(0.999305555555556),B9)

 

[RaydenUK]

WOW that works perfect!!! Thank you two so much, this is great.

 

[SpillerBD]

Try

=WORKDAY(A1+1,-1)+MOD(A1,1)

I like the simplicity, but the time constraint doesn't come out right. Is there a good method to capture that 23:59 constraint in your formula?

 

[Jonmo1]

Looks like I missed the part about making it specifically 11:59 PM.
I assumed we wanted the time to be the same as the original cell's time..

Try
=WORKDAY(A1+1,-1)+"23:59:00"