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
Melissa Parker 03Melissa Parker 03 

convert Date Time field to text in formula field

I have two reporting snapshots that run at different times, 12am PST Weekly and 1am PST Monthly, and saved to the same Source Object.

I'm trying to create a TEXT formula field that referenced the Snapshot Exeuction Time. I would like to display "Weekly" if the time is 12am and "Monthly" when the time is 1am. 

I also understand that the Snapshot Exeuction Time displays in GMT time. 

Im having difficulty creating this formula field. Can someone please assist? Thanks.
Ajay K DubediAjay K Dubedi
Hi Melissa,

Getting st, nd, etc... is going to be tougher. Below is a formula without that.

CASE(day(datevalue(CreatedDate)), 
1, "Sunday", 
2, "Monday", 
3, "Tuesday", 
4, "Wednesday", 
5, "Thursday", 
6, "Friday", 
7, "Saturday", 
"" 
) 
& 
", " 
& 
CASE(MONTH(datevalue(CreatedDate)), 
1, "January", 
2, "February", 
3, "March", 
4, "April", 
5, "May", 
6, "June", 
7, "July", 
8, "August", 
9, "September", 
10, "October", 
11, "November", 
12, "December", 
"None" 
) 
& 
" " 
& 
text(day(datevalue(CreatedDate))) 
& 
" at " 
& 
MID ( TEXT (CreatedDate ), 12, 5)

I hope you find the above solution helpful. If it does, please mark as Best Answer to help others too.
Thanks,
Ajay Dubedi
Melissa Parker 03Melissa Parker 03
I"m using the criteria below. Snapshot Execution time is a Date/Time field and I'm trying to grab just the TIME. based on the time, the formula should display either Weekly or Monthly

If(
TEXT(timevalue( Snapshot_Execution_Time__c ))="12:10:000.00","Weekly", "Monthly")

I'm havin trouble with the :10 AM portion of the formula. Perhaps I need to include the MINUTE function as well to capture :10. I'm receivings different errors.
Melissa Parker 03Melissa Parker 03
I"m using the criteria below. Snapshot Execution time is a Date/Time field and I'm trying to grab just the TIME. based on the time, the formula should display either Weekly or Monthly

If(
TEXT(timevalue( Snapshot_Execution_Time__c ))="12:10:000.00","Weekly", "Monthly")

I'm havin trouble with the :10 AM portion of the formula. Perhaps I need to include the MINUTE function as well to capture :10. I'm receivings different errors.
Melissa Parker 03Melissa Parker 03
I've been workign on this all day and still getting error's but I think I'm getting close:
If(
(hour(timevalue( Snapshot_Execution_Time__c )) == "12:10:00.000"),"Weekly", "Monthly")

Receiving error: Error: Incorrect parameter type for operator '='. Expected Number, received Text