Change D/M/YYYY to YYYY-MM

[Uri_Budnik]

i solved this with an ai column:

prompt: show month and year in YYYY-MM format of @endDate (this is the column)

but i am wondering what is a more efficient way to do this with a formula?. just looking to raise my coda game

there is a similar question question to this from last year: Formula to convert this date format (YYYY.MM.DD.) to (DD/MM/YYYY)

but i want to preserve the 2 digit month for sorting purposes. currently 1 digit months don’t have a leading zero. i can’t figure out a simple way to do that (mmm… maybe that is why ai is better for this…

in the link above the result is converted to a date in the end with todate(). in my case, coda does not store YYYY-MM as a date

in the formula below; endDate = 7/28/2023

thisRow.endDate.Split(“/”).WithName(splitted, Format(“{1}-{2}”, splitted.Nth(3), splitted.Nth(1)))

i can’t figure out how to take splitted.Nth(1)) and make sure that it has a leading zero when it is a single digit month

[Christiaan_Huizer]

hi @Uri_Budnik ,

you can use my suggestion and in the Format() function there is an option you need, have a look below:

Format("{1:00}/{2:00}/{3}",
  
thisRow.dateUSA.Day(),
thisRow.dateUSA.Month(),
thisRow.dateUSA.Year()
)

I hope it helps, Cheers, Christiaan

[Uri_Budnik]

many thanks!

now that you pointed me to it, i do see it in the documentation.