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
He came up with a clever solution.
- Let’s say you want to enter a series of dates..
- 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)
- Then enter your dates as follows e.g. 09072015
- Then in the column beside it, enter the following formula (note that I explain the functions in the video)
- =DATEVALUE(LEFT(A2,2)&”/”&MID(A2,3,2)&”/”&RIGHT(A2,4))
- Copy down and you have now generated a set of dates.
- Note that if you want to actually use them as dates, you may need to do a Copy – Paste Values.
- If they appear as numbers e.g. 42194, format them as Dates
You can view the video here…
[fvplayer src=”https://s3-eu-west-1.amazonaws.com/the-excel-expert/date_time_saver.mp4″]
For the time element, he did things slightly differently…
- The original time was entered as 0051 (again formatted as text)
- Then in the column beside it, he entered the following formula…=(LEFT(G2,1)&”:”&MID(G2,2,2)&”:”&RIGHT(G2,2))
- Note how the LEFT, MID and RIGHT are now linked with a colon…
- Note the contents of these cells are formatted as Time
- 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.
[fvplayer src=”https://s3-eu-west-1.amazonaws.com/the-excel-expert/date_time_saver.mp4″]
You can download the file here: date_time_saver