Home

Please help me with an Excel formula

%3CLINGO-SUB%20id%3D%22lingo-sub-774140%22%20slang%3D%22en-US%22%3EPlease%20help%20me%20with%20an%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-774140%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Everyone!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20trying%20to%20find%20a%20formula%20for%20the%20below%20scenario%20in%20Excel%20sheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20is%20an%20organisation%20where%20the%20pay%20is%20calculated%20on%20a%20weekly%20basis%20instead%20of%20daily%20or%20monthly.%26nbsp%3B%3C%2FP%3E%3CP%3E1.%20Need%20to%20find%20out%20the%20No.%20of%20weeks%20employee%20worked.%3C%2FP%3E%3CP%3E2.%20If%20employee's%20start%20date%20is%207%2F1%2F19%20End%20date%20is%207%2F5%2F19%20the%20output%20should%20be%201%20as%20the%20days%20are%20falling%20under%20same%20week.%3C%2FP%3E%3CP%3E3.%20If%20employee's%20start%20date%20is%207%2F1%2F19%20End%20date%20is%207%2F11%2F19%20the%20output%20should%20be%202%20as%20the%20days%20are%20falling%20under%202%20weeks%20and%20so%20on.%3C%2FP%3E%3CP%3E4.Here%2C%20there%20are%20few%20conditions%20to%20follow%3A%3C%2FP%3E%3CP%3Ea.If%20Employee%20work%20below%203%20days%20in%20a%20week%20as%20per%20the%20dates%207%2F1%2F19%20-%207%2F8%2F19%20the%20output%20should%20be%201%2C%20as%20the%20condition%20is%20less%20than%203%20days%20%3D%200%20week%2C%203%20and%20above%20%3D%201.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20find%20the%20attached%20screenshot%20and%20kindly%20help%20me%20with%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-774140%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-774384%22%20slang%3D%22en-US%22%3ERe%3A%20Please%20help%20me%20with%20an%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-774384%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20may%20consider%20the%20formula%20WEEKNUM%20and%20WEEKDAY.%20Please%20refer%20to%20the%20attached%20file.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20this%20help%20you.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F378575%22%20target%3D%22_blank%22%3E%40Vaman21%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-774432%22%20slang%3D%22en-US%22%3ERe%3A%20Please%20help%20me%20with%20an%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-774432%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F378575%22%20target%3D%22_blank%22%3E%40Vaman21%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%3C%2FP%3E%3CP%3EHere%20is%20a%20Complete%20Guide%20to%20date%20math.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DqIZxeOq-QDk%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DqIZxeOq-QDk%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Vaman21
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. 

 

Hope that this help you. 

 

@Vaman21 

@Vaman21 

Hi

Here is a Complete Guide to date math.

https://www.youtube.com/watch?v=qIZxeOq-QDk

 

Hope that helps

Nabil Mourad

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