INTRODUCTION
DATES and TIME , if you are new to Excel and you have already tried using those format, I am pretty sure that you must have felt panicky at some sort of time.
It is quite obvious because when we don’t know the exact working of any system or process, we try to use it just by hit and trial method. It works sometimes and sometimes not.
Here we are talking about Dates and Time in Excel. These are the tricky formats which we need frequently in our reports or charts.
Many times, we need to do operations on them. We need to put them in the conditions to trigger some event which makes it very important for us to learn the exact procedures to perform a task concerned with the dates and time.
In this article we would learn different tricks and methods to handle and manipulate Dates and Time formats so that they don’t mess up with our reports.
One of such trick is to find out the starting and ending date of the current week or any week .
BEFORE READING THIS ARTICLE , IT IS REQUESTED TO VISIT THE PART I WHICH DISCUSSES THE CONCEPT OF THE DATE AND TIME IN EXCEL FOR BETTER UNDERSTANDING.CLICK HERE TO VISIT.
FINDING OUT THE STARTING AND ENDING DATE OF THE CURRENT WEEK
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.
GET COMPLETE INFORMATION ABOUT THE WEEKDAY FUNCTION HERE.
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.
FINDING OUT THE WEEK START DATE AND WEEK END DATE FOR THE GIVEN DATE
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.
GET COMPLETE INFORMATION ABOUT THE WEEKDAY FUNCTION HERE.
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 GIVEN DATE
- Enter the date. [For our example date is put in T19.]
- For the WEEK STARTING DATE [Week starts on SUNDAY] enter the following function
- =T19-WEEKDAY(T19)+1
- For the WEEK ENDING DATE [Week ends on SATURDAY] enter the following function.
- =T19+(7-WEEKDAY(T19))
