+ Start a Discussion
Dave The RaveDave 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 CMaharajan 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

All Answers

Maharajan CMaharajan 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. 
https://success.salesforce.com/answers?id=90630000000D4gIAAS

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

Thanks,
Maharajan.C
Maharajan CMaharajan 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 RaveDave The Rave
Hi Maharajan,

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