Download Your Example Excel File
Formula to implement:
Date: The Excel DATE function returns a date value using year, month and date, sequentially.
Weekday: The Excel WEEKDAY function returns an integer between 1 and 7 the corresponds specific day of a week.
Formula Explanation (step by step)
Let’s break the formula into multiple small steps to understand its application.
First formula: DATE(B7,1,-2)
Syntax: =DATE(year, month, day)
It returns date after subtracting 2 days from the given date. here we subtract 2 days you can subtract any days.
The formula will first subtract date “29-12-2013” from 6 it returns “23-12-2013” and then add date after multiplying C7*7 (8*7) gives 56. Now add 56 dates to 23-12-2013 it returns 8th week with the day (Monday) by multiplying 7 i.e. “17-Feb-2014”.
Remember: To know the exact day name multiply it with 7 with dates.