Go to Qiita Advent Calendar 2024 Top
LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@Keiji_otsubo(Keiji Otsubo)

日付のフォーマットを変える

Last updated at Posted at 2021-09-29

yyyyMMdd

TEXT(YEAR(項目)) +
LPAD(TEXT(MONTH(項目)), 2, "0")  +
LPAD(TEXT(DAY(項目)), 2, "0") +

d/M/yyyy format

DATEVALUE(
RIGHT({!$Record.Datefield__c},4) & 
"-" &
IF((MID({!$Record.Datefield__c},3,1) ="/",
IF((MID({!$Record.Datefield__c},6,1) ="/",RIGHT({!$Record.Datefield__c},2)& "-"  & MID({!$Record.Datefield__c}4,2),RIGHT({!$Record.Datefield__c},2) & "-0" & MID({!$Record.Datefield__c}4,1)),
IF((MID({!$Record.Datefield__c},5,1) ="/","0" & RIGHT({!$Record.Datefield__c},1))& "-" &MID({!$Record.Datefield__c}3,2),"0" & RIGHT({!$Record.Datefield__c},1))& "0-" &MID({!$Record.Datefield__c}3,1))
)
)

DateTime型から日付をテキストで抜き取る

 left(TEXT(CreatedDate),FIND(' ', TEXT(CreatedDate))-1)

ISO 8601 date format

How do I format Created Date in the ISO 8601 date format?

TEXT(YEAR(DATEVALUE( CreatedDate ))) + "-" +
LPAD(TEXT(MONTH(DATEVALUE(CreatedDate))), 2, "0") + "-" +
LPAD(TEXT(DAY(DATEVALUE(CreatedDate))), 2, "0") +  
"T" +
LPAD(TEXT(HOUR(TIMEVALUE(CreatedDate))), 2, "0") + ":" +
LPAD(TEXT(MINUTE(TIMEVALUE(CreatedDate))), 2, "0") + ":" +
LPAD(TEXT(SECOND(TIMEVALUE(CreatedDate))), 3, "0") + "Z"

Date型、Time型の２つのフィールドから DateTime型のフィールドを作る

TEXT( Start_Date_Cstm_Date__c ) + " " + LEFT(TEXT( Start_Time__c ),8)

Wednesday, 29 September 2021 の表示に変える

WEEKDAY関数を使った方がよさそうです。

CASE(WEEKDAY(Date), 1, "Sunday",  2, "Monday",   3, "Tuesday",   4, "Wednesday",   5, "Thursday",   6, "Friday",    "Saturday" )
+ "," +
TEXT(DAY( Date ))
+ " " +
CASE(   MONTH( date ),   1, "January",   2, "February",   3, "March",   4, "April",   5, "May",   6, "June",   7, "July",   8, "August",   9, "September",   10, "October",   11, "November",   "December" )  
+ " " +
TEXT(YEAR( Date ))

Display date field in the format ddd dd-mmm-yyyy Eg: Tue, 20 Sep 2022

CASE(   MOD( date - DATE( 1900, 1, 7 ), 7 ),   0, "Sunday",   1, "Monday",   2, "Tuesday",   3, "Wednesday",   4, "Thursday",   5, "Friday",    "Saturday" )
+ "," +
TEXT(DAY( Date ))
+ " " +
CASE(   MONTH( date ),   1, "January",   2, "February",   3, "March",   4, "April",   5, "May",   6, "June",   7, "July",   8, "August",   9, "September",   10, "October",   11, "November",   "December" )  
+ " " +
TEXT(YEAR( Date ))

Formula field to show day of week and date in DDMMYYYY

Date/Time を Data型の MMDDYYYY フォーマットに変更する

LPAD(TEXT(MONTH(DATEVALUE([SVMXC__Service_Order__c].Scheduled_Date_End_Time__c))), 2, "0") + 
LPAD(TEXT(DAY(DATEVALUE([SVMXC__Service_Order__c].Scheduled_Date_End_Time__c))), 2, "0") + 
TEXT(YEAR(DATEVALUE([SVMXC__Service_Order__c].Scheduled_Date_End_Time__c)))

Hello, need some help converting a Date/Time field to Date, but with MMDDYYYY format.

時間を24時間形式から12時間形式に変換する

  • こっちが正しい

"Created By" field extracting time and AM/PM
Pull DateTime field into text field

ただし、GMTになっているので調整は必要

IF( 
       Value(LEFT(RIGHT(text( CreatedDate ),9),2)) < 12, 
       LEFT(RIGHT(text( CreatedDate ),9),2) + ":" + LEFT(RIGHT(text( CreatedDate ),6),2) + " AM",
 
       IF( 
              Value(LEFT(RIGHT(text( CreatedDate ),9),2)) = 12,
              LEFT(RIGHT(text( CreatedDate ),6),2) + ":" + LEFT(RIGHT(text( CreatedDate ),6),2) + " PM",
              TEXT(Value(LEFT(RIGHT(text( CreatedDate ),9),2)) - 12)) + ":" + LEFT(RIGHT(text( CreatedDate ),6),2) + " PM" 

   )

よくわからん。 はずだ、これは単に HH:MMにしかならん。騙された。

LEFT(RIGHT(text( CreatedDate ),9),8)

時間を12時間形式から24時間形式に変換する

GMTになっている。

formula on report to get the 24h format

#Auraのjs で時刻の変換。

Convert '12:15:00.000' to 12:15 PM

経過時間の表示

日にち

IF(FLOOR( (NOW()- CreatedDate)) > 0,TEXT( FLOOR( (NOW()- CreatedDate)) ) & " days ", "")

日時、秒

IF(FLOOR( (NOW()- CreatedDate)) > 0,TEXT( FLOOR( (NOW()- CreatedDate)) ) & " days ", "") 
& IF(FLOOR( MOD( (NOW()- CreatedDate)* 24, 24 ) )>0,TEXT( FLOOR( MOD( (NOW()- CreatedDate)* 24, 24 ) ) ) & " hours ","") 
& TEXT( ROUND( MOD( (NOW()- CreatedDate)* 24 * 60, 60 ), 0 ) ) & " minutes "
& TEXT( ROUND( MOD( (NOW()- CreatedDate)* 24 * 60*60, 60 ), 0 ) ) & " seconds"

選択リストの数値でない値を使って変換する　generate 4/1/2022.

IF(ISBLANK(TEXT(MonthsPicklist__c)), NULL,
DATE(
YEAR(TODAY()),
CASE(MonthsPicklist__c,
"January",1, 
"February",2, 
"March",3, 
"April",4,
"May",5,
"June",6, 
"July",7, 
"August",8, 
"September",9,
"October",10,
"November",11,12
), 1))

Formula to turn a picklist selection into a date

2021-01-18T05:00:00.000Z形式の文字列を日時型に変換する

DATETIMEVALUE(
SUBSTITUTE(
SUBSTITUTE( zulu_time__c ,'T',' '),'.000Z','')
)

和暦

[数式 サンプル] 日付型項目の値を和暦表示する数式

IF ( AND ( DATE__C >= DATE (1868,09,08), DATE__C <= DATE (1912,07,29)), "明治" & TEXT ( YEAR ( DATE__C ) - 1867 ) & "年" & TEXT ( MONTH ( DATE__C )) & "月" & TEXT ( DAY ( DATE__C )) & "日", 
IF ( AND ( DATE__C >= DATE (1912,07,30), DATE__C <= DATE (1926,12,24)), "大正" & TEXT ( YEAR ( DATE__C ) - 1911) & "年" & TEXT ( MONTH ( DATE__C )) & "月" & TEXT ( DAY ( DATE__C )) & "日", 
IF ( AND ( DATE__C >= DATE (1926,12,25), DATE__C <= DATE (1989,01,07)), "昭和" & TEXT ( YEAR ( DATE__C ) - 1925 ) & "年" & TEXT ( MONTH ( DATE__C )) & "月" & TEXT ( DAY ( DATE__C )) & "日", 
IF ( AND ( DATE__C >= DATE (1989,01,08), DATE__C <= DATE (2019,04,30)), "平成" & TEXT ( YEAR ( DATE__C ) - 1988 ) & "年" & TEXT ( MONTH ( DATE__C )) & "月" & TEXT ( DAY ( DATE__C )) & "日", 
IF ( DATE__C >= DATE (2019,05,01), "令和" & TEXT ( YEAR ( DATE__C) - 2018) & "年" & TEXT ( MONTH ( DATE__C )) & "月" & TEXT ( DAY ( DATE__C )) & "日", 
"#エラー")))))

Apex YYYY-MM-DDT00:00:00Zに変更する

String dateFormat = 'yyyy-MM-dd\'T\'HH:mm:ss\'Z\'';

DateTime dt = DateTime.now();
String dateString = dt.format(dateFormat);
System.debug(dateString);

I want to change the Date Time format of below line to YYYY-MM-DDT00:00:00Z

6-Nov-2023 とか 16-Nov-2023のような0埋めされてない場合

IF(MID(Custom__c,3,1) ="-",
DATE(VALUE(RIGHT(Custom__c, 4)),
CASE(MID(Custom__c, 4, 3),"Jan", 1, "Feb", 2, "Mar", 3, "Apr", 4, "May", 5, "Jun", 6,"Jul", 7, "Aug", 8, "Sep", 9, "Oct", 10, "Nov", 11, "Dec", 12, 0),
VALUE(LEFT(Custom__c, 2)),

DATE(VALUE(RIGHT(Custom__c, 4)),
CASE(MID(Custom__c, 3, 3),"Jan", 1, "Feb", 2, "Mar", 3, "Apr", 4, "May", 5, "Jun", 6,"Jul", 7, "Aug", 8, "Sep", 9, "Oct", 10, "Nov", 11, "Dec", 12, 0),
VALUE(LEFT(Custom__c, 1))
) - Custom_Date_Field__c

ADDMONTHSとの合わせ技

CASE(MONTH(ADDMONTHS( Ending_Date__c  , Month_number__c )),
1,"January", 
2,"February", 
3,"March", 
4,"April",
5,"May",
6,"June", 
7,"July", 
8,"August",
9,"September",
10,"October",
11,"November",
"December") + " " + 
TEXT(YEAR((ADDMONTHS( Ending_Date__c  , Month_number__c ))))
0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?