+ Reply to Thread
Results 1 to 4 of 4

Return different EDATE depending on BEGINNING of reference cell

  1. #1
    Registered User
    Join Date
    01-25-2021
    Location
    United States
    MS-Off Ver
    Office 365
    Posts
    8

    Return different EDATE depending on BEGINNING of reference cell

    Hi all,

    I am trying to return a particular EDATE - either 5 months or 7 months after the 'start date' in the reference cell - depending on if the start date begins with August or March.
    My desired scenario: If 'start date' begins with August (8), return value 7 months later. If 'start date' begins with March (3), return value for 5 months later.

    The following formula was my best guess:
    =IF(LEFT(D27,1)=8,EDATE(D27,7),EDATE(D27,5))

    The issue with this is that the value returned when only using the LEFT function is 4, because this is the date when formatted as text instead of short date. This can be formatted into a date, but I do not think you can do that when it is used within another function. Either way, I am only using the first number as a reference from the 'start date,' so it would return a date from the incorrect year. I will attach a photo.

    Any ideas on how to do this correctly?

    IF and EDATE .png

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,419

    Re: Return different EDATE depending on BEGINNING of reference cell

    Dates are just serial numbers formatted as dates.

    Try this:

    =IF(MONTH(D27)=8,EDATE(D27,7),EDATE(D27,5))
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    01-25-2021
    Location
    United States
    MS-Off Ver
    Office 365
    Posts
    8

    Re: Return different EDATE depending on BEGINNING of reference cell

    This worked perfectly. Thank you so much!!

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,419

    Re: Return different EDATE depending on BEGINNING of reference cell

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

+ 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: 1
    Last Post: 04-13-2018, 09:09 PM
  2. [SOLVED] VBA to add "0" at the beginning depending on cell value length
    By cloudberr in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-18-2015, 02:30 PM
  3. week beginning on a monday depending on month inputted
    By mfairhurst1 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-01-2013, 07:11 PM
  4. Return EDATE with an IF statement...? [SOLVED]
    By mirage1 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-21-2013, 02:15 PM
  5. [SOLVED] Insert 0 at beginning of field depending on no of characters
    By enna1949 in forum Excel General
    Replies: 5
    Last Post: 12-27-2012, 03:51 AM
  6. Replies: 14
    Last Post: 11-14-2012, 11:24 AM
  7. Change cell reference depending on date
    By freud1 in forum Excel General
    Replies: 2
    Last Post: 06-11-2009, 08:07 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