« IF Statement Wished Me (happy new year) | Show Invisible Cell Content ( its Advantages) » |
Hi there,
In this post, we will discuss about "How to find the number of specific days between two given dates?". Specific days is nothing but weekdays.
Eg: Howmany Sundays are there between two given dates? If you have a solution, stop reading this post and update your answers in the below given comments section.
If you have less time to explore here you go for a quick solution.
FUNCTIONS USED:
...
- SUMPRODUCT
- TEXT
- ROW
- INDIRECT
FORMULA:
=SUMPRODUCT(--(TEXT(ROW(INDIRECT(end_date&":"&start_date)),"DDDD")="specific_day"))
EXPLANATION:
INDIRECT function is used to convert given dates (text string) into a reference. This is because ROW accepts only reference as an argument.
TEXT function is used to display the resulting number into a text string, like "Sunday", "Monday" and etc.,
-- is called 'double minus', which means it converts text to values, eg: TRUE as 1 and FALSE as 0
SUMPRODUCT function is used to sum all the 1's and displays the total.
CONCLUSION:
Basically, there is no inbuilt function which is ready to use to achieve this result. So, we have worked around using multiple functions to achieve the desired results.
This formula will help to know how many specific days (name of the day) are available between given dates.
With this, any company or organization who are paying salary on a weekly basis to their employees can calculate the pay easily if at all employee(s) start date is different and the employees are getting the salary based on start date/ joining date. If few of the employees are ready to take salary based on their requirment, say two weeks once or four weeks once, then this formula will help the management with out any flaws.
*The above example is just one example and is only for your understanding.
That's it from my end. See you in the next awesome post.
DID YOU "SPECIFIC DAYS BETWEEN DATES"?
Please share your logic if at all you have come across in your work.
If you like this post, share it on your social networking sites by clicking on the icons placed around the blog.
If you want to receive such posts to your inbox, SIGN IN HERE, for free.
SOME IMPORTANT LINKS:
Explanation of VLOOKUP (video)
Count Number of Worksheets in a Workbook
INDEX/MATCH works with cell reference
Disclaimer: An image shown here is only to present "how to find similar person among public". This is to understand and show you the reference to the post.
Thanks,
You must be logged in to see the comments. Log in now!
If you have no account yet, you can register now! (It only takes a few seconds)
Recent comments