Loading

Formula to Return the Time, Taking Daylight Savings into Account

게시 일자: Apr 2, 2026
상세 설명

When retrieving the time portion of a Date/Time field through a formula, the time is in the GMT/UTC timezone. It is possible to offset the formula so it shows the time in a specific timezone. However, due to daylight saving time, the offset must be adjusted twice a year.

The formulas below allow you to dynamically calculate the correct time based on daylight saving rules for specific time zones.

솔루션

The below formulas return the time from a Date/Time field according to a specific timezone and its daylight saving rules.

Caveats

  • These are correct at the time of writing. Should the laws change regarding daylight savings in any country, the formulas will need to be adjusted.
  • These formulas only work for the time zones mentioned. If you are in a different one, adjust accordingly.
  • These formulas cannot be used in a Flow, as TIMEVALUE is not supported in Flow formulas.

Return Time in AM/PM Format

Supported Time Zones

  • East Coast Time zone
  • West Coast Time zone
  • GMT Time zone (UK, Ireland)
  • GMT+1 Time zone (France, Spain)

East Coast Time Zone

 
IF( OR(
DateTimeField< DATETIMEVALUE(DATE(YEAR(DATEVALUE(DateTimeField)),3,14)
-(WEEKDAY(DATE(YEAR(DATEVALUE(DateTimeField)),3,14))-1))+7/24,
DateTimeField>= DATETIMEVALUE(DATE(YEAR(DATEVALUE(DateTimeField)),11,7)
-(WEEKDAY(DATE(YEAR(DATEVALUE(DateTimeField)),11,7))-1))+7/24
),
IF(HOUR(TIMEVALUE(DateTimeField-5/24))=0,"12",
TEXT(
HOUR(TIMEVALUE(DateTimeField-5/24))
-IF(HOUR(TIMEVALUE(DateTimeField-5/24))>23,24,0)-
IF(HOUR(TIMEVALUE(DateTimeField-5/24))
-IF(HOUR(TIMEVALUE(DateTimeField-5/24))>23,24,0)>12, 12,0)))+":"+
LPAD(TEXT(MINUTE( TIMEVALUE(DateTimeField ))),2, "0")+
IF(HOUR(TIMEVALUE(DateTimeField-5/24))
-IF(HOUR(TIMEVALUE(DateTimeField-5/24))>23,24,0)>11, " P"," A"),
IF(HOUR(TIMEVALUE(DateTimeField-4/24))=0,"12",
TEXT(HOUR(TIMEVALUE(DateTimeField-4/24))
-IF(HOUR(TIMEVALUE(DateTimeField-4/24))>23,24,0)-
IF(HOUR(TIMEVALUE(DateTimeField-4/24))
-IF(HOUR(TIMEVALUE(DateTimeField-4/24))>23,24,0)>12,12,0)))
+":"+
LPAD(TEXT(MINUTE( TIMEVALUE(DateTimeField ))),2, "0")+
IF(HOUR(TIMEVALUE(DateTimeField-4/24))
-IF(HOUR(TIMEVALUE(DateTimeField-4/24))>23,24,0)>11, " P"," A")
)
+"M"
 

West Coast Time Zone

 
IF( OR(
DateTimeField< DATETIMEVALUE(DATE(YEAR(DATEVALUE(DateTimeField)),3,14)
-(WEEKDAY(DATE(YEAR(DATEVALUE(DateTimeField)),3,14))-1))+10/24,
DateTimeField>= DATETIMEVALUE(DATE(YEAR(DATEVALUE(DateTimeField)),11,7)
-(WEEKDAY(DATE(YEAR(DATEVALUE(DateTimeField)),11,7))-1))+10/24
),
IF(HOUR(TIMEVALUE(DateTimeField-8/24))=0,"12",
TEXT(
HOUR(TIMEVALUE(DateTimeField-8/24))
-IF(HOUR(TIMEVALUE(DateTimeField-8/24))>23,24,0)-
IF(HOUR(TIMEVALUE(DateTimeField-8/24))
-IF(HOUR(TIMEVALUE(DateTimeField-8/24))>23,24,0)>12, 12,0)))+":"+
LPAD(TEXT(MINUTE( TIMEVALUE(DateTimeField ))),2, "0")+
IF(HOUR(TIMEVALUE(DateTimeField-8/24))
-IF(HOUR(TIMEVALUE(DateTimeField-8/24))>23,24,0)>11, " P"," A"),
IF(HOUR(TIMEVALUE(DateTimeField-7/24))=0,"12",
TEXT(
HOUR(TIMEVALUE(DateTimeField-7/24))
-IF(HOUR(TIMEVALUE(DateTimeField-7/24))>23,24,0)-
IF(HOUR(TIMEVALUE(DateTimeField-7/24))
-IF(HOUR(TIMEVALUE(DateTimeField-7/24))>23,24,0)>12,12,0)))
+":"+
LPAD(TEXT(MINUTE( TIMEVALUE(DateTimeField ))),2, "0")+
IF(HOUR(TIMEVALUE(DateTimeField-7/24))
-IF(HOUR(TIMEVALUE(DateTimeField-7/24))>23,24,0)>11, " P"," A")
)
+"M"
 

GMT (UK, Ireland)

 
IF( OR(
DateTimeField<= DATETIMEVALUE(DATE(YEAR(DATEVALUE(DateTimeField)),3,31)
-(WEEKDAY(DATE(YEAR(DATEVALUE(DateTimeField)),3,31))-1) +1/24),
DateTimeField> DATETIMEVALUE(DATE(YEAR(DATEVALUE(DateTimeField)),10,31)
-(WEEKDAY(DATE(YEAR(DATEVALUE(DateTimeField)),10,31))-1)+1/24)
),
IF(HOUR(TIMEVALUE(DateTimeField))=0,"12",
TEXT(
HOUR(TIMEVALUE(DateTimeField))
-IF(HOUR(TIMEVALUE(DateTimeField))>23,24,0)-
IF(HOUR(TIMEVALUE(DateTimeField))
-IF(HOUR(TIMEVALUE(DateTimeField))>23,24,0)>12, 12,0)))+":"+
LPAD(TEXT(MINUTE( TIMEVALUE(DateTimeField ))),2, "0")+
IF(HOUR(TIMEVALUE(DateTimeField))
-IF(HOUR(TIMEVALUE(DateTimeField))>23,24,0)>11, " P"," A"),
IF(HOUR(TIMEVALUE(DateTimeField+1/24))=0,"12",
TEXT(
HOUR(TIMEVALUE(DateTimeField+1/24))
-IF(HOUR(TIMEVALUE(DateTimeField+1/24))>23,24,0)-
IF(HOUR(TIMEVALUE(DateTimeField+1/24))
-IF(HOUR(TIMEVALUE(DateTimeField+1/24))>23,24,0)>12,12,0)))
+":"+
LPAD(TEXT(MINUTE( TIMEVALUE(DateTimeField ))),2, "0")+
IF(HOUR(TIMEVALUE(DateTimeField+1/24))
-IF(HOUR(TIMEVALUE(DateTimeField+1/24))>23,24,0)>11, " P"," A")
)
+"M"
 

GMT+1 (France, Spain)

 
IF( OR(
DateTimeField<= DATETIMEVALUE(DATE(YEAR(DATEVALUE(DateTimeField)),3,31)
-(WEEKDAY(DATE(YEAR(DATEVALUE(DateTimeField)),3,31))-1) +1/24),
DateTimeField> DATETIMEVALUE(DATE(YEAR(DATEVALUE(DateTimeField)),10,31)
-(WEEKDAY(DATE(YEAR(DATEVALUE(DateTimeField)),10,31))-1)+1/24)
),
IF(HOUR(TIMEVALUE(DateTimeField+1/24))=0,"12",
TEXT(
HOUR(TIMEVALUE(DateTimeField+1/24))
-IF(HOUR(TIMEVALUE(DateTimeField+1/24))>23,24,0)-
IF(HOUR(TIMEVALUE(DateTimeField+1/24))
-IF(HOUR(TIMEVALUE(DateTimeField+1/24))>23,24,0)>12,12,0)))
+":"+
LPAD(TEXT(MINUTE( TIMEVALUE(DateTimeField ))),2, "0")+
IF(HOUR(TIMEVALUE(DateTimeField+1/24))
-IF(HOUR(TIMEVALUE(DateTimeField+1/24))>23,24,0)>11, " P"," A"),
IF(HOUR(TIMEVALUE(DateTimeField+2/24))=0,"12",
TEXT(
HOUR(TIMEVALUE(DateTimeField+2/24))
-IF(HOUR(TIMEVALUE(DateTimeField+2/24))>23,24,0)-
IF(HOUR(TIMEVALUE(DateTimeField+2/24))
-IF(HOUR(TIMEVALUE(DateTimeField+2/24))>23,24,0)>12,12,0)))
+":"+
LPAD(TEXT(MINUTE( TIMEVALUE(DateTimeField ))),2, "0")+
IF(HOUR(TIMEVALUE(DateTimeField+2/24))
-IF(HOUR(TIMEVALUE(DateTimeField+2/24))>23,24,0)>11, " P"," A")
)
+"M"
 

Return Military Time (Hour Only)

Supported Time Zones

  • East Coast Time zone
  • West Coast Time zone
  • GMT
  • GMT+1

East Coast Time Zone

 
IF( OR(
DateTimeField< DATETIMEVALUE(DATE(YEAR(DATEVALUE(DateTimeField)),3,14)
-(WEEKDAY(DATE(YEAR(DATEVALUE(DateTimeField)),3,14))-1))+7/24,
DateTimeField>= DATETIMEVALUE(DATE(YEAR(DATEVALUE(DateTimeField)),11,7)
-(WEEKDAY(DATE(YEAR(DATEVALUE(DateTimeField)),11,7))-1))+7/24
),
HOUR(TIMEVALUE(DateTimeField-5/24))
-IF(HOUR(TIMEVALUE(DateTimeField-5/24))>23,24,0),
HOUR(TIMEVALUE(DateTimeField-4/24))
-IF(HOUR(TIMEVALUE(DateTimeField-4/24))>23,24,0))
 

West Coast Time Zone

 
IF( OR(
DateTimeField< DATETIMEVALUE(DATE(YEAR(DATEVALUE(DateTimeField)),3,14)
-(WEEKDAY(DATE(YEAR(DATEVALUE(DateTimeField)),3,14))-1))+10/24,
DateTimeField>= DATETIMEVALUE(DATE(YEAR(DATEVALUE(DateTimeField)),11,7)
-(WEEKDAY(DATE(YEAR(DATEVALUE(DateTimeField)),11,7))-1))+10/24
),
HOUR(TIMEVALUE(DateTimeField-8/24))
-IF(HOUR(TIMEVALUE(DateTimeField-8/24))>23,24,0),
HOUR(TIMEVALUE(DateTimeField-7/24))
-IF(HOUR(TIMEVALUE(DateTimeField-7/24))>23,24,0))
 

GMT (UK, Ireland)

 
IF( OR(DateTimeField<= DATETIMEVALUE(DATE(YEAR(DATEVALUE(DateTimeField)),3,31)-(WEEKDAY(DATE(YEAR(DATEVALUE(DateTimeField)),3,31))-1) +1/24),
DateTimeField> DATETIMEVALUE(DATE(YEAR(DATEVALUE(DateTimeField)),10,31)- (WEEKDAY(DATE(YEAR(DATEVALUE(DateTimeField)),10,31))-1)+1/24)),
HOUR(TIMEVALUE(DateTimeField))
-IF(HOUR(TIMEVALUE(DateTimeField))>23,24,0),
HOUR(TIMEVALUE(DateTimeField+1/24))
-IF(HOUR(TIMEVALUE(DateTimeField+1/24))>23,24,0))
 

GMT+1 (France, Spain)

 
IF( OR(DateTimeField<= DATETIMEVALUE(DATE(YEAR(DATEVALUE(DateTimeField)),3,31)-(WEEKDAY(DATE(YEAR(DATEVALUE(DateTimeField)),3,31))-1) +1/24),
DateTimeField> DATETIMEVALUE(DATE(YEAR(DATEVALUE(DateTimeField)),10,31)- (WEEKDAY(DATE(YEAR(DATEVALUE(DateTimeField)),10,31))-1)+1/24)),
HOUR(TIMEVALUE(DateTimeField+1/24))
-IF(HOUR(TIMEVALUE(DateTimeField+1/24))>23,24,0),
HOUR(TIMEVALUE(DateTimeField+2/24))
-IF(HOUR(TIMEVALUE(DateTimeField+2/24))>23,24,0))
 
Knowledge 기사 번호

000394902

 
로드 중
Salesforce Help | Article