I'm trying to find a formula for the below scenario in Excel sheet.
There is an organisation where the pay is calculated on a weekly basis instead of daily or monthly.
1. Need to find out the No. of weeks employee worked.
2. If employee's start date is 7/1/19 End date is 7/5/19 the output should be 1 as the days are falling under same week.
3. If employee's start date is 7/1/19 End date is 7/11/19 the output should be 2 as the days are falling under 2 weeks and so on.
4.Here, there are few conditions to follow:
a.If Employee work below 3 days in a week as per the dates 7/1/19 - 7/8/19 the output should be 1, as the condition is less than 3 days = 0 week, 3 and above = 1.
Please find the attached screenshot and kindly help me with this.
You may consider the formula WEEKNUM and WEEKDAY. Please refer to the attached file.
Hope that this help you.
Here is a Complete Guide to date math.
Hope that helps