1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Formula in Excel for a Date to Remain Constant Based on a Range of Dates

Discussion in 'Business Applications' started by Beartide, May 18, 2012.

Thread Status:
Not open for further replies.
  1. Beartide

    Beartide Thread Starter

    Joined:
    May 18, 2012
    Messages:
    1
    Our employees can sell such things as vacation time, for example, 1 day of vacation pay but their request must be submitted by a certain time if they are to be paid. We can tell them what day they will be paid based on the date they submit their request. This can be confusing for a supervisor to explain and I wanted to automate it on an Excel sheet.
    Example:
    1. Employees receive their regular check every Friday as well as a check for any time sold.
    2. As long as they get it into us within 7 days prior to Friday they will receive their check the next Friday.
    3. If itÂ’s greater than 7 days then it would be the Friday after next before they received the sold time check.
    4. For example If an employee requested on May, 12th 13th, 14th, 15th, 16th, or 17th to sell vacation time that check would be received by the next Friday i.e. May 25th. However, if they turned it in on May 18th. 19th. 20th, 21st, 22nd., 23nd, or 24th. they would receive it on June 1st.
    5. This pattern continues throughout the year. Note that weekends are included.
    6. I think the attached will better explain what I am trying to do.
     

    Attached Files:

  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    I think there is something wrong in the way you explain it but, could be my interpretation though
    I adden a column using the Days360() function which shows the nr of days between the request date and the 'friday' date, maybe you can use that resulty to compute the new value.
     

    Attached Files:

  3. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    I changed the formula in the column for you and think this is what you are asking.
    You can always replace the reference to column F for the formula
     

    Attached Files:

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1053751

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice