12. Combine Dates and Times

 
Subtitles Enabled

Sign up for a free trial to continue watching this lesson.

Free trial

Overview

Dates and times should ideally be handled stored as one value. Here I show you how to do this for all the entries in our dataset.

Lesson Notes

Keyboard shortcuts

SHIFT + : Select adjacent cell
CTRL + SHIFT + #: Switch to General format
ALT + E, S, T: Paste format
ALT + E, S, V: Paste values
CTRL + : Move to end of data region
CTRL + SHIFT + : Select all cells within data region
CTRL + SPACEBAR: Select column
ALT + E, D: Delete selected column(s)

Note: The COUNTIF formulas need to be updated with the new DateTime column as well - I have completed this off-camera

Transcript

If you scroll down through our data set using the page down key, you'll notice that our users are not arranged in chronological order.

Ideally, I'd like to sort our users in the order they signed up based on date and time.

To do this, we could create two sort levels, the first which sorts the data first by date, and then the second level, which sorts the data by time.

But a better solution would be to combine date and time time into one value and then sort based on this value.

So how would this work? I'll start by moving the time column next to the date column and creating a new column called Date time of camera.

And in the first row I'll format all of the values as general with Ctrl + Shift + #.

You might recall from the first lesson in this course that dates are counted from the first of January 1900, and so the first of January 2012 is 40,909 days from the first of January 1900.

You might also recall that times are stored as a fraction of a day.

So to combine these two values, we can simply add them together.

So I'll right “=time” plus the date, and this gives me 40,909.3976 in the general format.

All we need to do now is to take this value and change its format so that it shows the date and the time in one cell.

This format exists in the custom list.

So I'll press Ctrl + 1 to go to the format cells dialogue box and C twice to get to the custom list.

I'll then go to the scroll bar and scroll down to find the desired format.

And eventually, I'll find it.

When I press OK, you can see that the date time is converted into the exact format that we want.

I can now autofill for the remaining cells.

Once we've converted the first row of date and time back into the correct format with Alt + E S T, we can now perform our sort on the Date time column.

So I'll select the full data set with Ctrl + Shift + Right Arrow, Ctrl + Shift + Down Arrow, and then Alt + A S S to open up the sort dialogue box.

I'll then sort by date time and press OK.

And as you can see from the Date time column, we now have our data arranged in chronological order.

The date and time columns are now redundant, but before we remove them we must break the link between the date time formulas and these cells.

To do this, I'll select the date time values, Ctrl + C to copy and then paste values with Alt + E S V.

And as you can see, this removes the formula and just leaves the value.

We can now delete the date and time columns.

Ctrl + Space bar to select, and Alt + E D to delete.

Our last task would be to fix the week number function which accepted the date value which we've just removed.

So I'll press F2 to jump back inside the formula, and then delete the error, and F2 again to select a cell.

I'll select the date time value and press enter.

And I'll autofill by double clicking in the bottom right hand corner, so that the WEEKNUM now works correctly again.

If possible, I always try to keep dates and times in one value, because it reduces the number of variables you will have in formulas containing these values and it will reduce the likelihood of errors.

In the next lesson, we'll look at times in more detail and understand how to add and subtract these values correctly.