Format Date/Time fields from Time Log Exports

Here is a guide on how to Edit the Date/Time Formatting for the Time Log exports when required:

Create a column next (does not matter which side) to the column of data you wish to format. This column will be temporary.

In the top cell of the new column you just created, to the left/right of the column with the data you wish to format, use the “TEXT” function in the following way:

“=TEXT(<cell>, <format style>)” e.g. “=TEXT(C2, "dd/mm/yyyy”)”

After you’ve populated the new column with new the newly formatted data, select all the new data, right click and select “Copy”.

Now, select all of the old data you wish to replace with this new data, right click and select “Paste Special…”.

There will be a few options shown, select “Values” from the “Paste” section and click “OK”. The new data should replace the old data.

Delete the temporary column.

See the attached gifs below for examples of formatting Dates and Times with this method.

Date:


Time:



  • Select “File” and choose “Save As…”.

  • Save the file as a .xlxs/.csv file (whichever is required)


How did we do?