Date Formats in Worksheet

Ipshita

Just for knowledge -

Someone had raised this question about date formats in the community and I wanted to add the response to it so it might help some others as well. A mini disclaimer here that most of you might already know this but sometimes we all forget the most obvious things that are in front of our eyes so just take this in jest and don't hate me for pointing out the obvious! :)

So, in case any of you are looking to change the date format in your working sheet to anything different from your computer/regional settings, the easiest way is to go to settings in the left hand panel and hit the regional preferences option. Here you can modify the date format and your sheet/forms etc. will reflect accordingly.

Hope this helps :)

Cheers!

Ipshita

Garrett.Ricker

is there a way to change the format to to show 1/1/23 as January 1, 2023 or any other variation of date format?

Kevin M.

@Garrett.Ricker The only method I've found to do this involved manually building out a lookup to match 1-12 with January-December, and then using a formula to format the date. It works well if it's just for human readability, but it's not useable as a calculating field.

Here's an example using [Date]@row as the date, though any date field can be referenced:

=VLOOKUP(WEEKDAY([Date]@row), {weekdays}, 2) + ", " + VLOOKUP(MONTH([Date]@row), {months}, 2) + " " + DAY(TODAY()) + ", " + YEAR(TODAY())

The two helper ranges, {weekdays} and {months} should be on a separate sheet if the main sheet is used as a table, otherwise sorting and filtering will break them. My ranges look like this:

Row | Number | Name |

1 | 1 | January |

2 | 2 | February |

...

12 | 12 | December |

and then

14 | 1 | Sunday |

15 | 2 | Monday|

...

20 | 7 | Saturday |

The formula takes the digit of the month and the digit of the weekday and returns the word equivalent., and VLOOKUP works well since they're in numerical order and there are no duplicates.