function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
InterstateAdminInterstateAdmin 

Concatenate two date fields

I'm trying to concatenate two date fields (Arrival Date, Departure Date) into a new field I've called Week.

On the object itself, the date fields are coming across as MM/DD/YYYY, but when I look at the new field they are coming across YYYY-MM-DD.  I'd like the result to be MM/DD/YYYY - MM/DD/YYYY.

My formula looks like this:

TEXT(Arrival_Date__c) & " - " & TEXT(Departure_Date__c)

I've tried changing things around, but can't seem to get anything to change the result.

Is it possible?

Thank you!
Jason Curtis NBSFDGJason Curtis NBSFDG
Hi, you'll need to break down the dates in the formula using MONTH, DAY & YEAR, fairly straight forward:

TEXT(MONTH(Arrival_Date__c) & "/" & DAY(Arrival_Date__c) & "/" & YEAR(Arrival_Date__c) & "-" & MONTH(Departure_Date__c) & "/" & DAY(Departure_Date__c) & "/" & YEAR(Departure_Date__c))

Some good general info here: https://help.salesforce.com/HTViewHelpDoc?id=formula_examples_dates.htm&language=en_US#getting-dmy-from-date (https://help.salesforce.com/HTViewHelpDoc?id=formula_examples_dates.htm&language=en_US#getting-dmy-from-date)
InterstateAdminInterstateAdmin
Thank you!  I had to make a slight change (had to add TEXT to the front of each option), but it works perfectly.

Final formula:

TEXT(MONTH(Arrival_Date__c)) & "/" & TEXT(DAY(Arrival_Date__c)) & "/" & TEXT(YEAR(Arrival_Date__c)) & " - " & TEXT(MONTH(Departure_Date__c)) & "/" & TEXT(DAY(Departure_Date__c)) & "/" & TEXT(YEAR(Departure_Date__c))

Thanks again!
InterstateAdminInterstateAdmin
I have a new problem with my field...

My dates are coming out perfectly, except when I try to report on them.  I'd like it to appear in chronological order, but it's not working.

Should be:
9/1/2014 - 9/5/2014
9/8/2014 - 9/12/2014
9/29/2014 - 10/3/2014

But instead it appears like this:

9/1/2014 - 9/5/2014
9/29/2014 - 10/3/2014
9/8/2014 - 9/12/2014

I'm sure it's because 2 comes before 8, but I don't know how to fix it.  How can I turn the 8 into an 08?  (So, 9/08/2014 instead of 9/8/2014)

Possible?

Thank you!
Jason Curtis NBSFDGJason Curtis NBSFDG
Hi, you'll need to put an if statement in there to test if the day is less than 10 you can add a 0 to it. Then it will be sorted correctly. If you need help with it, let me know!
InterstateAdminInterstateAdmin
Thank you!  I couldn't figure out the IF statement, but got it to work (I used CASE).  I'm 100% sure it's not what a true developer would do, but it works!

Thanks again!
Jason Curtis NBSFDGJason Curtis NBSFDG
Good job & a developer is as a developer does! If you get a chance, post your final formula, helpful for others.
InterstateAdminInterstateAdmin
I changed it where it was showing the name of the month and the day, instead of what I originally wanted.  So, here it is in all its glory!

Arrival_Month__c & " " &
CASE(
DAY( Arrival_Date__c),
1, "01",
2, "02",
3, "03",
4, "04",
5, "05",
6, "06",
7, "07",
8, "08",
9, "09",
10, "10",
TEXT(DAY( Arrival_Date__c))
)& " - " &
Departure_Month__c & " " &

CASE(
DAY( Departure_Date__c),
1, "01",
2, "02",
3, "03",
4, "04",
5, "05",
6, "06",
7, "07",
8, "08",
9, "09",
10, "10",
TEXT(DAY( Departure_Date__c))
)

It now renders Month DD - Month DD (ex. August 18 - August 22).

I'd be happy to take feedback if there is a "prettier" way to do it.  Thank you!