A reader of this newsletter (who wishes to remain anonymous), send me a really interesting file which he uses to quickly enter dates and times. Dates and times can be a bit fiddly to enter as they have to be entered with / for dates and : for times.

Saving time

Saving time

He came up with a clever solution.

  1. Let’s say you want to enter a series of dates..
  2. Format the cells as text (this means you can enter days like 01 or months like 04 without worrying about the zeros disappearing -as they do when you enter 04 in cells formatted as number)
  3. Then enter your dates as follows e.g. 09072015
  4. Then in the column beside it, enter the following formula (note that I explain the functions in the video)
  5. =DATEVALUE(LEFT(A2,2)&”/”&MID(A2,3,2)&”/”&RIGHT(A2,4))
  6. Copy down and you have now generated a set of dates.
  7. Note that if you want to actually use them as dates, you may need to do a Copy – Paste Values.
  8. If they appear as numbers e.g. 42194, format them as Dates

You can view the video here…

For the time element, he did things slightly differently…computers_time

  1. The original time was entered as 0051 (again formatted as text)
  2. Then in the column beside it, he entered the following formula…=(LEFT(G2,1)&”:”&MID(G2,2,2)&”:”&RIGHT(G2,2))
  3. Note how the LEFT, MID and RIGHT are now linked with a colon…
  4. Note the contents of these cells are formatted as Time
  5. Now they are still formulas so if we want to use them as pure time entries, we will have to do a Copy – Paste Values again. 

You can download the file here: date_time_saver


Leave a Reply