Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
ShowAll Questionssorted byDate Posted
Dave The Rave

# Formula for date/time field

Reposted - Date/Time field Formula help

I have an existing date/time field

RegistrationDate_c = example value 31/12/2019 14:34

I would like to create a new date/time formula field based on the date from RegistrationDate__c, so need a formula here.

New field is ProcessDate__c

ProcessDate__c = the date from RegistrationDate__c and time must be always 23:59

So if RegistrationDate__c = 01/12/2019 15:45 then ProcessDate should be 01/12/2019 23:59

Can anyone help? I have searched documentation online but cannot find the syntax for the formula.

Yes, you guessed this field will be used to trigger a process.

Regards Dave
Best Answer chosen by Dave The Rave
Maharajan C
Hi Dave,

If RegistrationDate_c  is Date Field then refer the below formula. In above i have consider this field as Date/Time.

DATETIMEVALUE( TEXT( RegistrationDate_c  ) +" "+ "23:59:00" ) - 0.2291

Thanks,
Maharajan.C

Maharajan C
Hi Dave,

You have to use the below formula:

And in below formula i have used the - 0.2291. This is for converting the formula field result in to users local time zone. See my time zone is GMT+5.30 so i have used 0.2291 ( +5.30 means 330 minutes extra from GMT and for 24 hours the total mins is 1440 . so 330/1440 = 0.2291 ). Please do this calculation as per your timezone.

DATETIMEVALUE( TEXT( DATEVALUE( RegistrationDate_c ) ) +" "+ "23:59:00" ) - 0.2291

Thanks,
Maharajan.C
Maharajan C
Hi Dave,

If RegistrationDate_c  is Date Field then refer the below formula. In above i have consider this field as Date/Time.

DATETIMEVALUE( TEXT( RegistrationDate_c  ) +" "+ "23:59:00" ) - 0.2291

Thanks,
Maharajan.C
This was selected as the best answer
Dave The Rave
Hi Maharajan,

This works perfectly. Thanks for. your time and effort.