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
amrit

# 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

T-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.

T-Han

• 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..

amrit

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

amrit

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-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..

amrit
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-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
amrit

Hi,

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