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
amritamrit 

Need formula for time field based on timezone

Hi,

 

I have created datetime field named  SitevisitDateTime.Now i need to extract time and date field seperately.

Im able to extract  date field correctly.How to display time in a formula field.

 

I did like this .

MID (TEXT( Site_Visit_Date__c ), 12, 8 ) .This will not show exact time .I need 12 hr format and timezone is time zone is 
(GMT+05:30) India Standard Time (Asia/Calcutta) .How can i accomplish this?

 

 

Thanks

Best Answer chosen by Admin (Salesforce Developers) 
T-HanT-Han

Yes the Syntax is correct but the co-relation of Text to Number is off the grid.

Try this.. 

 

MID (TEXT (NOW() +0.220851), 12, 5)

 

Return Type : Text 

 

Lemme know.

 

 

 

All Answers

T-HanT-Han

This might help you as I had troubles with the Date/Time.

 

  • VALUE (MID(TEXT(NOW()-0.25002), 12, 2))is a Text formula field and it the current time (now)-0.25002 which indicates GMT-6 hours(CENTRAL TIME ZONE, CHECK FOR WHICH TIME ZONE YOU WUD LIKE) (1 hour = 0.04167). [12, 2] indicates the place where the hour is present in the now () format and 2 mean takes 2 digits. (Example. 01, 02,12,22,24..). 

You can first place this formula in a Text field (Or number - check this one) then use the calc of your part.

 

Let me know if this works or need more expl..
amritamrit

Thanks for your reply.I will check in the instance and will update on this
amritamrit

Hi,

 

 

I checked in the instance.Its is displaying the time but not showing minutes.Eg: Its showing time as 9 not 9:45.

As per your suggestion i have added 0.220851 based upon timezone GMT+5.30 

This will show the time in hrs only.how to display the minutes.

 

Thanks
T-HanT-Han

VALUE (MID(TEXT(NOW()-0.25002), 12,2))  Instead of 2 type in 4. As 2 indicates the no: of characters to be used from the 12th position in the  format. You need to fiddle with the 2 as 4 or any number. You will get the mins.. If you use 6 you will also get the seconds.

 

Lemme know..
amritamrit
Hi,

I have tried the same.See i tried like thisVALUE (MID(TEXT(NOW()+0.220851),
12, 6)).

Return type is number .Decimal places -2

In the page its showing ERROR#
T-HanT-Han

Yes the Syntax is correct but the co-relation of Text to Number is off the grid.

Try this.. 

 

MID (TEXT (NOW() +0.220851), 12, 5)

 

Return Type : Text 

 

Lemme know.

 

 

 

This was selected as the best answer
amritamrit

Hi,

 

Its working now. May i know how to add AM or PM into this field.

 

Thanks for your help

 
T-HanT-Han

In this case you can't as its converting the Time to Time Zone of 24 hrs format.. So AM/PM wont be a possible notion.