+ Reply to Thread
Results 1 to 41 of 41

Left and Right function for Date.

  1. #1
    Registered User
    Join Date
    10-14-2018
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    Microsoft Office 2016
    Posts
    37

    Left and Right function for Date.

    Hi,

    I encountered two problems here while dealing with date (eg. 31/1/2018).
    1. I tried to use RIGHT function to extract the year, =right(the cell, 4), and I get 3111 instead of 2018.
    2. As the 1 in the middle means January, I hope to extract "1" from there, is there any function could do so?
    Any help would be much appreciated. Thanks!
    3111.PNG

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Left and Right function for Date.

    Use
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    10-14-2018
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    Microsoft Office 2016
    Posts
    37

    Re: Left and Right function for Date.

    Hi TMS,

    thanks for your reply, but it seems that can't be done for me.
    Capture.PNG

  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,404

    Re: Left and Right function for Date.

    What formatting do you have on that column?
    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.

  5. #5
    Registered User
    Join Date
    10-14-2018
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    Microsoft Office 2016
    Posts
    37

    Re: Left and Right function for Date.

    Hi Ali, glad to have you again. =)

    It is just value.
    Capture.PNG

  6. #6
    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,404

    Re: Left and Right function for Date.

    Upload the workbook. We can't troubleshoot a picture.

  7. #7
    Registered User
    Join Date
    10-14-2018
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    Microsoft Office 2016
    Posts
    37

    Re: Left and Right function for Date.

    The attached will be the file.
    Thanks!
    Attached Files Attached Files

  8. #8
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,482

    Re: Left and Right function for Date.

    when you use the Year() (or month()) function you need to change the celformatting from date to number or General. as the outcome is a normal value and not a datevalue (or praticly it still is but 2018 is day 2018 since 1900 so that is why you see 10 juli 1905)

    So what you need to know, proper formatted dates are a number with a formatting mask that will show them as a date. so you cannot apply the text functions LEFT and RIGHT of the underlying value as that will show (as you noticed) a part of the number. the underlying value of 11 jan 2018 is 43111 so that is why you see 3111 with RIGHT(Cell;4)

    so when finding parts of dates you use function YEAR, MONTH or DAY but then need to change celformat (back) to General (or number)
    Last edited by Roel Jongman; 03-29-2019 at 05:01 AM.

  9. #9
    Registered User
    Join Date
    10-14-2018
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    Microsoft Office 2016
    Posts
    37

    Re: Left and Right function for Date.

    Hi Roel,

    Thousand thanks for the explanation. I got it now.
    Btw, is there any function can I do like my 2nd issue? Given that I want to extract "A" from 234A345, and its cell format is in general.

  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: Left and Right function for Date.

    what do you mean that you want to extract "A" from 234A345? Do you have many of these and they vary in location and by letter?
    it can be done as simply as use find and replace, find what >> A, replace with what >> leave blank
    Another way is simply =SUBSTITUTE(A1,"A","") and that will return 234345 in another cell.
    And if you need it to be numeric use this =--SUBSTITUTE(A1,"A","") or this =SUBSTITUTE(A1,"A","")*1
    But if you have many of these with various letters then it can become more complicated.
    Details are helpful.

    BTW, I don't see any of those in the workbook you did attach, so you might need to upload another workbook with examples for that request.
    Last edited by Sam Capricci; 03-29-2019 at 07:36 AM.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  11. #11
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,482

    Re: Left and Right function for Date.

    I agree with sambo kid we need more info if this is about extracting letters from a string.

    But in your opening post you mention as second problem that you want to extract the month 1 (january) from the date.
    So if that is what you are asking the the answer is simple. Where YEAR(the cell) finds the year of a datecell, the function MONTH(the cell) will find the month from that same cell.

  12. #12
    Registered User
    Join Date
    10-14-2018
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    Microsoft Office 2016
    Posts
    37

    Re: Left and Right function for Date.

    I'm sorry to be vague on my questions.
    I am still want to get the month 1 as per my post.
    The data was actually copy paste from a system (which I use for personal finance recording).
    Roel's guide has totally been helpful for me, appreciate that, but it is only applicable to when the cell is in date.
    For example, the cell showing "31/1/2019" and cell format is in date, after changing cell format to general, it would become number, said 43111.
    And I can use "=YEAR" in that case.
    However, there are some error while copy pasting the data, which some date were originally cell formatted in general.
    Which mean the cell is showing "31/1/2019" and the cell format is in general. Which is different from previous example.
    The attached is the data file with the date, after you change the column A's cell format into general,
    you will found that some cell will still remain as "31/1/2019", which I couldn't use "=YEAR".
    Attached Files Attached Files

  13. #13
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: Left and Right function for Date.

    In attached file YEAR function is working properly on each data in column A.
    Happy with my answer * Add Reputation.
    If You are happy with solution, please use Thread tools and mark thread as SOLVED.

  14. #14
    Registered User
    Join Date
    10-14-2018
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    Microsoft Office 2016
    Posts
    37

    Re: Left and Right function for Date.

    Hi Kokosek, this is what I got here.
    Do you mind to have a look?
    Capture.PNGCapture.PNG

  15. #15
    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,404

    Re: Left and Right function for Date.

    No problem here:

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    81
    13/02/2019 Cash
    2019
    82
    13/02/2019 6431-4465-12
    2019
    83
    13/02/2019 Cash
    2019
    84
    13/02/2019 Cash
    2019
    85
    13/02/2019 Cash
    2019
    86
    02/12/2019
    Cash
    2019
    87
    02/12/2019
    Cash
    2019
    88
    02/12/2019
    Cash
    2019
    89
    02/12/2019
    Cash
    2019
    90
    02/11/2019
    6431-4465-12
    2019
    91
    02/11/2019
    Cash
    2019
    92
    02/11/2019
    Cash
    2019
    Sheet: 2018-11-01_2019-02-28

  16. #16
    Registered User
    Join Date
    10-14-2018
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    Microsoft Office 2016
    Posts
    37

    Re: Left and Right function for Date.

    Hi Ali,

    would it possible to be the problem of my Excel version?
    Capture.PNG
    Edit: Btw, I'm not sure if it is because of this error.

  17. #17
    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,404

    Re: Left and Right function for Date.

    Are you using a Mac?

  18. #18
    Registered User
    Join Date
    10-14-2018
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    Microsoft Office 2016
    Posts
    37

    Re: Left and Right function for Date.

    Nope, it's Windows 10.

  19. #19
    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,404

    Re: Left and Right function for Date.

    Then which version of Excel are you using? There is no such thing as Microsoft 2017.

  20. #20
    Registered User
    Join Date
    10-14-2018
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    Microsoft Office 2016
    Posts
    37

    Re: Left and Right function for Date.

    It's Microsoft Office 2016.
    That's a mistake, I will edit it.
    Thanks.

  21. #21
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: Left and Right function for Date.

    Try
    Please Login or Register  to view this content.
    if you will get error it means that DATE isn't in proper format (I am not saying about cell formatting).

  22. #22
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,482

    Re: Left and Right function for Date.

    Ah, ok. These screenshots raise a HUGE red flag.. All your dates in the first 12 days of the month get messed up this happens when files have a different date format then your local system often if the data comes from csv or other non excel formats. if you set all cells in column B to you local date format. (I think that is month/day/year) then you will see strange jumps in dates and that all dates of day 13 and higher are texts..

    looking at your screenshots I can show you the problem (I cannot show you thru a file because my system is on d-m-y format so gets all the dates right)

    look at row 85 there is the text "13/02/2019" which is in words 13 February 2019 and is in Excel datevalue 43509

    based on the logic that row 79 show 14 feb and row 78 15 feb
    you would expect in row 86 to see 12 february 2019 or value 43508 (1 lower then 13/02) however on the screenshot it is valu 43801
    and if you format that cell as date you will see the date is 2 december 2019 and NOT 12 februari.

    The problem is that your local system has month-day-year as default date format. And the file you opened has day-month-year formatted strings.
    and due to automated processes of excel all dates with a day value of lower then 12 in the day-month-year format get interpreted the first number before the / as the month. so cells that show a date are mostlikely wrong.

    you will need to fix this before continuing.. how does this data come to excel? do you export it from another system? is that a different filetype like txt of csv?

  23. #23
    Registered User
    Join Date
    10-14-2018
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    Microsoft Office 2016
    Posts
    37

    Re: Left and Right function for Date.

    Hi Koko, do you mean this?
    Capture.PNG

  24. #24
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: Left and Right function for Date.

    Exactly. It looks like cell B85 does not contain date (number)
    Try then:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    maybe it helps.
    Also, recommend Roel's post about local setting.
    Last edited by KOKOSEK; 03-29-2019 at 10:59 AM.

  25. #25
    Registered User
    Join Date
    10-14-2018
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    Microsoft Office 2016
    Posts
    37

    Re: Left and Right function for Date.

    Hi Roel,

    truly appreciate the efforts you given in noticing the problem.
    I got the data from a PC system of a book-keeping mobile APP.
    Capture.PNG

  26. #26
    Registered User
    Join Date
    10-14-2018
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    Microsoft Office 2016
    Posts
    37

    Re: Left and Right function for Date.

    This is what I got here.
    Capture.PNG

  27. #27
    Registered User
    Join Date
    10-14-2018
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    Microsoft Office 2016
    Posts
    37

    Re: Left and Right function for Date.

    The format of the file seems alright with .xls
    Capture.PNG

  28. #28
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: Left and Right function for Date.

    Please Login or Register  to view this content.
    change slash to dash
    and also could you tell what you will get for:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    I am still blind shooting because it is working in my excel.
    Last edited by KOKOSEK; 03-29-2019 at 11:09 AM.

  29. #29
    Registered User
    Join Date
    10-14-2018
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    Microsoft Office 2016
    Posts
    37

    Re: Left and Right function for Date.

    Thank you for giving patience here, Koko.

    Capture.PNGCapture1.PNG

  30. #30
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: Left and Right function for Date.

    Type 2 so we've got TEXT there.
    Change order in date set:
    Please Login or Register  to view this content.

  31. #31
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,482

    Re: Left and Right function for Date.

    In reply to post 25 with the moneymanager screenshot..
    I see dates there formatted as year-month-day.. that would be an OK format for export, then Excel will recognize it as correct dates.
    then all you need to do once you saved the file locally is reformat to show it as how you want.

    I have no knowledge of that program but please look if you can influence the export format eighter as year-month-day or if you can tell it to export dates as your local format. then the problem should be solved.


    If you have to fix the dates in excisting files that has mi of text and number dates as they are now. you can put a "bushfix" formula in a help column to make sure all dates are converted to the correct datevalue.

    try this formula for that
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    this will find the dates in both text values with the right, mid and left functions
    and for the dates that are already as date but most likely wrong the 2nd DATE function flips day and month position back.

    please test against the original data in the program you export from.
    Best would be if you can fix the export.

  32. #32
    Registered User
    Join Date
    10-14-2018
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    Microsoft Office 2016
    Posts
    37

    Re: Left and Right function for Date.

    Hi Koko, do you mean this?
    Capture.PNG

  33. #33
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: Left and Right function for Date.

    Use Roel's formula it should solve issue.

  34. #34
    Registered User
    Join Date
    10-14-2018
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    Microsoft Office 2016
    Posts
    37

    Re: Left and Right function for Date.

    Hi Roel, I tried your formula, but I'm not sure whether I execute it right.
    Capture.PNG
    I tried to search through buttons, and have no idea if I can reformat the date.
    Attached Images Attached Images

  35. #35
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: Left and Right function for Date.

    There is not Roel's formula in A85 on your picture.

  36. #36
    Registered User
    Join Date
    10-14-2018
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    Microsoft Office 2016
    Posts
    37

    Re: Left and Right function for Date.

    I tried this too.
    Capture.PNG
    Is the result is what we looking?

  37. #37
    Registered User
    Join Date
    10-14-2018
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    Microsoft Office 2016
    Posts
    37

    Re: Left and Right function for Date.

    Sorry Koko, I uploaded the wrong image, please see the latest screenshot I uploaded.

  38. #38
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: Left and Right function for Date.

    You got date now. Change cell format into Date and voila.

  39. #39
    Registered User
    Join Date
    10-14-2018
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    Microsoft Office 2016
    Posts
    37

    Re: Left and Right function for Date.

    Woah, it is resolved!!
    It's a pleasure to have you guys here.
    Million thanks on the help.
    Will read and learn through the formulas used there.

    Credit to KOKOSEK, Roel Jongman, AliGW, TMS, Sambo kid.

  40. #40
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: Left and Right function for Date.

    You welcome.

    IF you happy with solution please use Thread tools and mark thread as SOLVED.

  41. #41
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Left and Right function for Date.

    You're welcome. Thanks for the rep.

+ 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] Embed left function in match/index function
    By Kyle18 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-12-2016, 05:17 AM
  2. Macro to find Date Cells > Current Date and delete everything to left of that cell
    By LewisLonsdale in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-25-2015, 06:36 AM
  3. LEFT Function to read the first date in a cell
    By orayner2 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-28-2014, 01:47 PM
  4. [SOLVED] IF Function referencing IsNumber, Match, Left function on separate sheets
    By Touch9713 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-12-2013, 10:09 PM
  5. Combining Mid Right Left with DATE function
    By marshymell0 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-11-2013, 03:14 PM
  6. Using date format and left function with worksheet name to hide worksheet
    By rgullett in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-21-2012, 05:16 PM
  7. HOW DO I NEST THE VLOOKUP FUNCTION WITH THE LEFT FUNCTION
    By CHAIM in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-27-2005, 04:10 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