In this post we will see how we could show the date as Friday’s date if the date falls on weekend.
There are many scenarios in excel where we need to have only working days dates for several calculating purpose, so in this post we will see how we can have previous Fridays date if the date calculated falls on weekend.
Below screenshot shows us the situation where column A shows the actual date that is being calculated ,column B shows the corresponding day and in column C we will write formula to show Friday’s date if the date in column A falls on weekend.
We aim to write the formula based on date in column A , if date in column A resolves to a Sunday or Saturday then our formula will automatically change the date to previous Friday.
We will use Workday formula in excel to change the dates to Friday’s date in case the date is Saturday or Sunday.
Workday formula in excel returns the date before or after the specified number of workdays and we manipulate the formula by using +1 and -1 to adjust our formula for weekdays.
Enter the formula =WORKDAY(A2 +1,-1) in cell C2 to get the date of previous Friday and press enter.
You can see here that in the first cell as we have date calculating to Tuesday, the new date remains same, now drag the formula downwards to calculate new dates for row 3 and 4.
We see here that as the date in cell A3 and A4 corresponds to Saturday and Sunday respectively, the new dates show us Friday’s date in column C.
Hope this helped.
