+ Reply to Thread
Results 1 to 12 of 12

Defining custom date formats and the DATEDIF function

  1. #1
    Registered User
    Join Date
    09-29-2020
    Location
    Montreal, Canada
    MS-Off Ver
    2016
    Posts
    6

    Defining custom date formats and the DATEDIF function

    Hi all,

    I have a large stack of papers containing "start" and "end" dates that require data entry into excel. These dates are written in the following format:

    YEARMMDD, where MM is a two letter code for each month (e.g. SE is september, OC is october, etc).

    For example, start date: 2020SE10 end date: 2020SE14.

    Rather than mentally converting this data into one of Excel's built-in formats, is there a way to define this custom format so that excel recognizes it? One that would also allow me to perform operations such as:

    =DATEDIF(2020SE10,2020SE14,"d")

    Thank you

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Defining custom date formats and the DATEDIF function

    What do you have for June and July?
    AND March and May?
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Registered User
    Join Date
    09-29-2020
    Location
    Montreal, Canada
    MS-Off Ver
    2016
    Posts
    6

    Re: Defining custom date formats and the DATEDIF function

    In order from January to December:
    JA
    FE
    MR
    AL
    MA
    JN
    JL
    AU
    SE
    OC
    NO
    DE

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Defining custom date formats and the DATEDIF function

    are you willing to use a helper table, like somewhere else on the workbook putting JA in a cell and adjacent to it 1, then under JA put FE and across from it 2 etc.?

    AND, how do the days numbered 1 through 9 show up in the data, as 2020SE01 or 2020SE1?

  5. #5
    Registered User
    Join Date
    09-29-2020
    Location
    Montreal, Canada
    MS-Off Ver
    2016
    Posts
    6

    Re: Defining custom date formats and the DATEDIF function

    Yes, that's no problem. How do I apply the helper table?

    The date always has two characters: 2020SE09

  6. #6
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Defining custom date formats and the DATEDIF function

    Ok, I made some assumptions, you have empty cells you can use and helper columns are ok. So assuming in A2 is 2020SE10 and B2 is 2020SE14, I put this formula in C2 and dragged to D2...
    =LEFT(A2,4)&VLOOKUP(MID(A2,5,2),$M$2:$N$13,2,FALSE)&RIGHT(A2,2)
    In cells M2 to N13 I put in (in M2) JA and N2 1 then in M3 FE and N3 2, etc.
    after using the table in M2:N13 and the vlookup embedded in the formulas in C2 and D2 then I put the date dif formula in E2 =DATEDIF(C2,D2,"d")

    OR, you could consolidate both dates into this one datdif formula which still looks at A2 and B2
    =DATEDIF(LEFT(A2,4)&VLOOKUP(MID(A2,5,2),$M$2:$N$13,2,FALSE)&RIGHT(A2,2),LEFT(B2,4)&VLOOKUP(MID(B2,5,2),$M$2:$N$13,2,FALSE)&RIGHT(B2,2),"d")
    Then you don't need to use the helper columns in C2 and D2.

    Adjust the formulas to the locations that suit your needs.

  7. #7
    Registered User
    Join Date
    09-29-2020
    Location
    Montreal, Canada
    MS-Off Ver
    2016
    Posts
    6

    Re: Defining custom date formats and the DATEDIF function

    Thank you for the detailed explanation. The consolidated formula is particularly attractive, but it begins to break down when the start and end months are the different (see attached image). A one month difference between 2021FE10 and 2021MR10 is computed as a 100 day difference.

    Attachment 697568

  8. #8
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Defining custom date formats and the DATEDIF function

    I cannot open your attachment, it comes up as invalid.
    BUT, you can change the date dif formula to this...
    =DATEDIF(DATE(LEFT(A2,4),VLOOKUP(MID(A2,5,2),$J$2:$K$13,2,FALSE),RIGHT(A2,2)),DATE(LEFT(B2,4),VLOOKUP(MID(B2,5,2),$J$2:$K$13,2,FALSE),RIGHT(B2,2)),"yd")
    that corrected the numbers for me in my example.
    again, repoint it to your data.

  9. #9
    Registered User
    Join Date
    09-29-2020
    Location
    Montreal, Canada
    MS-Off Ver
    2016
    Posts
    6

    Re: Defining custom date formats and the DATEDIF function

    Yes, that works perfectly. Is there any reason why you used "yd" instead of "d" in the formula?

  10. #10
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Defining custom date formats and the DATEDIF function

    sorry, it is an old habit, actually you should probably use "d" instead of "yd" because if you have year cross over "yd" ignores the year change while "d" will not.
    so for example, if you had 2020FE01 and 2021MR10 (more than a year later) "yd" will return 38 - counting the days between February 1 and March 10 but ignoring the year change while "d" will return 403 because it takes into account the change in years.

  11. #11
    Registered User
    Join Date
    09-29-2020
    Location
    Montreal, Canada
    MS-Off Ver
    2016
    Posts
    6

    Re: Defining custom date formats and the DATEDIF function

    Thank you once again, your help was invaluable!

    Have a nice day

  12. #12
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Defining custom date formats and the DATEDIF function

    You're welcome, glad I could help.

+ 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. If date is between two periods from a DatedIf function
    By elleb in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-27-2018, 05:05 AM
  2. Automatically stop counting date difference in datedif function
    By Md Aftab Uzzaman in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-13-2018, 08:33 PM
  3. [SOLVED] Custom Date Formats 00/01/900
    By richgoof in forum Excel General
    Replies: 5
    Last Post: 01-22-2014, 09:20 AM
  4. Date Formats:Custom
    By Quagga in forum Excel General
    Replies: 1
    Last Post: 07-18-2011, 06:05 AM
  5. Date and Time picker custom formats
    By Newbie_Nick in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-25-2010, 08:58 AM
  6. [SOLVED] Custom number formats in TEXT function
    By MatthewB in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-28-2006, 04:05 PM
  7. Custom date formats
    By Bhupinder Rayat in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-07-2005, 04:06 PM

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