+ Reply to Thread
Results 1 to 8 of 8

If two sets of dates fall within another two set of dates

  1. #1
    Registered User
    Join Date
    03-30-2020
    Location
    India
    MS-Off Ver
    2016
    Posts
    4

    Post If two sets of dates fall within another two set of dates

    Dear Members i need help with formula using Dates, where i need look up if these dates fall between another two date,

    Leave Start Date - 1/19/2020 (Column B2)
    Leave End Date - 2/01/2020 (Column B3)

    I have two more columns where sate provides disability payment.

    State Disability Benefit Start date - 1/20/2020 (Column E2)
    State Disability Benefit End date - 1/20/2020 (Column E3)


    State provides Disability benefit of $1000 per week. (Column H2)


    I would like to find out the total benefit amount that employee receives through State, if Leave start date and Leave end date falls between State Disability Benefit start date and End Date then i need to know the total number of days where employee is receiving state benefits and total amount. So that i can deduct this amount pay remaining salary

    Please help me


    Thank you
    Harsha

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,684

    Re: If two sets of dates fall within another two set of dates

    so do these two dates have to be between the state dates OR if they finish after, do you just pay up to the state end date ?
    Also for days do you payout at 5 or 7 days a week
    or is that $1000 a Sun-Sat rate ?
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    03-30-2020
    Location
    India
    MS-Off Ver
    2016
    Posts
    4

    Re: If two sets of dates fall within another two set of dates

    Hello Wayne,

    Thank you very much for responding to my query. here is the brief description of what happens.

    If employee is going on Paid Leave of absence then employee will receive certain portion of his wages from respective state government. We will reduce whatever amount state has paid from his wages and pay the remaining portion to ensure EE is receiving 100 % Pay.

    Example :- If total salary is $2000 per week (7 days). and receiving benefit of $1000 per week (7 days) from state government. We will pay the remaining $1000 so that employee is getting paid 100% of salary. (State benefit $1000 + $1000 from employer = 2000 ).

    Example New Jersey states pays $1000 per week (7 Days including Saturday and Sunday). California pays $1300 per week.

    State benefits can be paid right from the leave start date or after the leave start date. below are two examples where state benefits are starting on leave start date and after leave start date.

    Leave Start Date - 1/19/2020
    Leave End Date - 3/01/2020

    State Disability Benefit Start date - 1/19/2020
    State Disability Benefit End date - 3/1/2020

    Or
    Leave Start Date - 1/19/2020
    Leave End Date - 3/01/2020

    State Disability Benefit Start date - 1/26/2020
    State Disability Benefit End date - 2/29/2020.

    Please review the latest attachment which is the actual calculator which i am trying to build to use it for all pay periods (Weekly & Bi-weekly & Monthly)


    Thank you,
    Harsha

  4. #4
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,684

    Re: If two sets of dates fall within another two set of dates

    i dont understand the excel - looking at row 10 & 11
    ROW 10
    1 01/03/2020 14/03/2020 14
    Thats 2 weeks, and so the offset amount from benefit is $1000.00 for each week & so 2000
    BUT you show 1000
    is that correct & if so why ?

  5. #5
    Registered User
    Join Date
    03-30-2020
    Location
    India
    MS-Off Ver
    2016
    Posts
    4

    Re: If two sets of dates fall within another two set of dates

    Yes, that's correct it should be 2000 for 14 days that was a mistake.

    Row 10 dates - 1 01/03/2020 14/03/2020 14 falls under State Benefit dates so it should be 2000
    Row 12 dates - 3/29/2020 4/4/2020 7 - These days doesn't come under state benefits so it should not deduct anything.

  6. #6
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,684

    Re: If two sets of dates fall within another two set of dates

    I have played with some helper columns and tested to some extent - Maybe worth keeping those helpers so you can see how the amount is built up

  7. #7
    Registered User
    Join Date
    03-30-2020
    Location
    India
    MS-Off Ver
    2016
    Posts
    4

    Re: If two sets of dates fall within another two set of dates

    Magnificent! You’re a lifesaver!

    I cannot thank you enough for your help! You have no idea how much this has helped me. I have tested it and it is working fine.

    If both leave and benefit starts and ends on the same date then it should show 1 day. but it is not picking the value as 1. This something I will try to figure it out.

    Thank you,
    Harsha

  8. #8
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,684

    Re: If two sets of dates fall within another two set of dates

    OK,
    can you give the example - i get one day
    I am adding 1 day
    =IF(J10="","",(K10-J10)+1)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 12-09-2019, 03:17 AM
  2. [SOLVED] Creating a serious of dates, where the dates never fall on the weekend
    By blackrosepetals9169 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-06-2018, 03:22 AM
  3. Check if range of dates fall between two dates
    By Wasilsky in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-14-2017, 08:40 AM
  4. Replies: 6
    Last Post: 03-11-2013, 06:11 PM
  5. [SOLVED] Averaging values if desired dates fall between range of dates
    By gbcpurdue in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-21-2012, 02:51 PM
  6. Need Sumif to count dates that fall within a range of dates
    By Paralegal101 in forum Excel General
    Replies: 1
    Last Post: 09-12-2011, 02:26 PM
  7. Replies: 5
    Last Post: 10-26-2005, 02:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1