CONCEPT:
This solution is for the situation when we need to find out the starting and the ending date of the week.
It can be the requirement that we need to put the data in a date slab.
For example it is 2nd July 2020 today. We need to enter the data for the day under the week 28-6-2020 to 4-6-2020 which is a complete week. Let us try to find it out automatically.
The current date is given by TODAY().
and there is a function called WEEKDAY() which returns the DAY number of the week i.e. 1 for the Sunday and 7 for Saturday.
WEEKDAY function takes the argument as
=WEEKDAY(serial_number,[return_type])
The first argument goes through the serial number and second is the return type. The return type decided the way the days are counted for example if Sunday is 1 or Monday is 1 and so on.
For the example , we will take the standard working week i.e. from MONDAY TO FRIDAY with SATURDAY and SUNDAY OFF.
STEPS TO FIND OUT THE STANDARD WEEK START DATE AND WEEKEND DATE OF THE CURRENT WEEK
- Enter the date.
- For the WEEK STARTING DATE [Week starts on SUNDAY] enter the following function
- =TODAY()-WEEKDAY(TODAY())+1
- For the WEEK ENDING DATE [Week ends on SATURDAY] enter the following function.
- =TODAY()+(7-WEEKDAY(TODAY()))
If you study the formulas, these are simple manipulations but they would work for us flawlessly.
LET US GENERALIZE THE FORMULA FOR ANY DATE AND FINDING OUT THE WEEKSTART DATE AND WEEKEND DATE.
We’ll just put the date in place of TODAY() in the above mentioned formulas.