I think this will be my most obscure blog post - but it’s a problem I came up against when dealing with formatting for data entry and struggled to find a solution.

When working with data files for import - you’ll often require the date be stored in a specific format. Excel can be helpful for this - or it can be a nightmare - especially for example when you want to save exact values for a CSV file.

The ‘text’ function in Excel can be used to convert a date to a specific field - and importantly store it in that format without any cleverness.

Man in a watch typing
Photo by Brad Neathery / Unsplash

First you’ll need to add an extra column to the right of the dates you want to convert -

In the top cell we want to enter the formula -

=TEXT(A2,"yyyy-mm-dd hh:MM:ss")

You can then fill this formula down the sheet for each row in the file -

Now you’ve got your dates in the desired format - but it’s from a formula - so we’re going to want to ‘paste values’ into another column, typically the original source data.

You can then delete the extra column with converted dates.

Your date column now contains correctly formatted data - and this won’t be lost on save. You can also adjust the formula to deal with alternate date formats.

An obscure article I know - but maybe it helps someone out!