+ Reply to Thread
Results 1 to 5 of 5

Dates with nested if funcitons

  1. #1
    Registered User
    Join Date
    12-13-2018
    Location
    Ferndale, WA
    MS-Off Ver
    Microsoft Office Standard 2016
    Posts
    15

    Dates with nested if funcitons

    Hello.

    I am trying to create a function for pay raise due dates. I want to have the due date be a year past when they were hired (or last received a raise), but if it falls in the beginning of the month, it returns to the 1st, if it falls in the end of the month it returns the 16th.

    Basically my thought is if someone was hired on 1/5/19 in column A, column B would show 1/1/20 and subsiquently if someone was hired on 4/29/18, column B would show 4/16/19.

    I have tried a few nested formulas that could bring about this, but none of them have worked. They all come back with errors. The one I most recently tried is:

    =DATE(YEAR(A1)+1,MONTH(A1),DAY(IF(A1<=15,"1","16")))


    Every time I put in a date in A1, the day comes back 16 (False). My guess is the cell is treating the whole date like a number, so 1/5/18 looks like 42918, which would mean every date would be greater than 15. Am I right in this?

    Would people be able to help me with this function?

    Thanks.

  2. #2
    Forum Expert dosydos's Avatar
    Join Date
    12-09-2015
    Location
    Massachusetts
    MS-Off Ver
    365(PC) V:2202
    Posts
    1,468

    Re: Dates with nested if funcitons

    try this out Assuming dates start in A1:


    =IF(DAY(A1)>15,DATE(YEAR(A1)+1,MONTH(A1),16),DATE(YEAR(A1)+1,MONTH(A1),1))

    this assumes someone hired on the 16th to the end of the month will get a raise on the 16th. Anyone hired from the 1st - 15th would get a raise on the 1st.

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,601

    Re: Dates with nested if funcitons

    Try it like this:

    =DATE(YEAR(A1)+1,MONTH(A1),IF(DAY(A1)<=15,1,16))

    Hope this helps.

    Pete

  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: Dates with nested if funcitons

    try
    =DATE(YEAR(A1)+1,MONTH(A1),DAY(IF(DAY(A1) < =15,1,16)))
    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.

  5. #5
    Registered User
    Join Date
    12-13-2018
    Location
    Ferndale, WA
    MS-Off Ver
    Microsoft Office Standard 2016
    Posts
    15

    Re: Dates with nested if funcitons

    Thanks! This helps a lot!

+ 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. [SOLVED] Nested If Functions for Upcoming Expiration Dates and Expired Dates
    By AmberLeanne in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-12-2017, 07:33 PM
  2. [SOLVED] IF with nested SUM COUNTIFs with dates
    By mg989 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-17-2017, 09:08 AM
  3. NESTED IFs with Time and dates
    By Newsome79 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-05-2016, 07:20 AM
  4. Replies: 12
    Last Post: 06-13-2014, 05:03 PM
  5. [SOLVED] Help with Nested IFS and Dates
    By Lizzy1965 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-09-2014, 08:25 AM
  6. If/And Nested Statement between dates
    By noobtoexcel in forum Excel General
    Replies: 4
    Last Post: 03-02-2012, 12:12 PM
  7. adding tooltips / helper tips to custom funcitons
    By Scott in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-11-2005, 02:06 AM

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