Date formatting

Hey spreadsheet masters.

I am using the app:logins first entry per user as a “registration date”. I’ve created a duplicate spreadsheet where I added a column that was meant to have the formatted date but for some reason I am not being able to format it. I’ve tried formatting it with the sheets formatting and with text(Datecolum, “dd/mm/yyyy”) formula.

Does anyone know what I can do?

Thanks in advanced

I don’t think Glide is saving it as a true date value. It’s appears to just be text. Try this formula. It strips out the T and Z, then converts it to a date value which can be reformatted using TEXT:

=TEXT(DATEVALUE(MID(A2:A,1,10)) + TIMEVALUE(MID(A2:A,12,8)), "DD/MM/YYYY")

1 Like

Thank you so much @Jeff_Hager. Worked like a charm.

1 Like

@Jeff_Hager I have a choice component that display date as “Sun 16/01/2020” and need to change it back from text to date value in the format of “dd/mm/yyyy”. I have tried Seting date from within google sheets, Tried To_date, Datevalue etc … but in vain.

How can I do that?

Would this formula work for you? Put your date in A3.

=date(right(A3,4), mid(A3,8,2), mid(A3,5,2))
1 Like

Perfect @George_B. That certainly solved my issue. Thanks

Thanks @George_B I am trying to add a range of minutes from 10:00 am to 11:30 pm to a singular date and it’s not working.

I am trying to add a 30-minute range to start of business hour 10:00 am till closing time at 11:30 pm to a singular date and it’s working. To elaborate Mon 20/01/2020 need to add to it 30-minute time interval till it reached 11:30 pm and displayed vertically in this format

Mon 20/01/2020 10:30 am
Mon 20/01/2020 11:00 am
Mon 20/01/2020 11:30 am ….etc

I’ve used this formula but is not working as it should, Thanks for the help

=ArrayFormula(TIME(10,row(C1:C23)*30,0)+C2)

Check out the Dates Example sheet here: