Dates format

harrinho

New member
Joined
Mar 22, 2019
Messages
2
Reaction score
0
Points
0
Excel Version(s)
16.22
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?


View attachment date mapping.xlsx
 
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?
 

Attachments

  • harrinho-navic9906.xlsx
    19 KB · Views: 10
Thank you very much Navic. That's gold, I thought there was an easiest way but that works, awesome
 
You're welcome, thanks for the feedback
 
Back
Top