• 396K Members
• 3,580 Online
• 431K Conversations

Highlighted
New Contributor

Hello Everyone!

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.

2 Replies

You may consider the formula WEEKNUM and WEEKDAY. Please refer to the attached file.

Hi

Here is a Complete Guide to date math.

Hope that helps

This tutorial is a Festival of Date Formulas & Functions! With over 40 examples that will allow you to hold a firm grasp of Date Math. I also included on a separate sheet a full definition of each function with the arguments required to use them. You can Download the Exercise file and Follow along
Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
201 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies