Results 1 to 5 of 5

Thread: Dates format

  1. #1
    Neophyte harrinho's Avatar
    Join Date
    Mar 2019
    Posts
    2
    Articles
    0
    Excel Version
    16.22

    Dates format



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi everyone,


    As simple as it might seem I struggle with the mapping of the days at the format they come through Salesforce export. So, in the attached I have a list of dates for Salesforce opportunities in the sfdc tab, and I want to assign a number of day next to these dates (i.e. 16/3/2019 to be Day 1, 17/3/2019 to be Day 2 etc.)

    What I want is to apply a format in the date in Sheet 1 that when I paste the list of date as pulled from Salesforce in SFDC tab, to map the Day Join column and don't have errors.

    Any ideas what this format should be please?


    date mapping.xlsx

  2. #2
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    882
    Articles
    0
    Excel Version
    Excel 2013
    Your dates are not the same format on Sheet 'SFDC' and 'Sheet1'.
    On Sheet 'SFDC' dates are formed as text, these dates should be converted to format like 'Sheet1'.
    If you are looking for a formula on the 'SFDC' sheet then try this below.
    This formula converts the text expression to the actual date, then finds the date on 'Sheet1' and returns the result from the 'B' column.
    Code:
    =IFERROR(VLOOKUP(DATE(VALUE(RIGHT(A2;4));VALUE(IFERROR(IF(LEN(A2>10);LEFT(A2;SEARCH("/";A2)-1);A2);A2));VALUE(REPLACE(LEFT(A2;LEN(A2)-5);1;FIND("/";A2);"")));Sheet1!$A$2:$B$48;2;FALSE);"")
    Did it help?
    Attached Files Attached Files
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  3. #3
    Neophyte harrinho's Avatar
    Join Date
    Mar 2019
    Posts
    2
    Articles
    0
    Excel Version
    16.22
    Thank you very much Navic. That's gold, I thought there was an easiest way but that works, awesome

  4. #4
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    882
    Articles
    0
    Excel Version
    Excel 2013
    You're welcome, thanks for the feedback
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  5. #5
    Neophyte SheltonDel's Avatar
    Join Date
    Apr 2019
    Posts
    1
    Articles
    0
    Excel Version
    2013
    So you have to keep the format the same between sheets to avoid this, right?

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •