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
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
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies