Date functions

Worksheet: Dates
Gives you the difference between two dates in whatever unit of measure you specify. Useful for calculating age from a birthdate.

=Datedif(Date 1, Date 2, Unit of Measure)

Units of Measure:
“y” --- years
“m” ---months
“ym” ---number of months since the last year

You can use the TODAY() function to refer to today’s date. Or you could put a specific date in there (with quotes around it)

Examples:
=Datedif(b2,today(), “y”)
=Datedif(b2, “1/1/2004”, “y”)

Worksheet: Dates
These functions allow you to grab just one piece of a date. So, if you have a series of dates and you want a new field that just gives the year, or the month.

=Year(Datefield)

=Month(Datefield)

=Day(Datefield)

If have 4/3/04, here’s what you’ll get with each formula:

Year: 2004
Month: 4
Day: 3 (it gives the date, as in the 3rd day of the month)

Worksheet: Dates

This returns the day of the week (Monday, Tuesday, etc). However the results come out as 1 (for Sunday), 2 (for Monday), etc.

=Weekday(Datefield)

Note: If you want the 1 value to represent Monday (then 2 for Tuesday, 3 for Wednesday, et), add a 2 on to the formula like this:

=weekday(datefield,2)

To display the results as abbreviations instead of 1 through 7: Go to Format > Cells and choose Custom and type “ddd” in the Type box provided. It will display 1 as “Sun”, 2 as “Mon”, etc. However, the underlying information will remain as numbers. So if you want to base an IF..THEN statement on this field or something like that, your formula would need to refer to the numbers. Also, it will sort the days alphabetically, not chronologically.

Worksheet: DateConvert

Excel has a couple functions for dealing with situations when you have data that needs to be a true date, but isn't.

Situation 1: You imported data and the field with the date comes through as text. It looks like a date, but it's not acting like a date. In this case, use the DATEVALUE() function.
=datevalue(a2)

Situation 2: You have year, month and day stored in separate columns and you want to combine them. For this you will will use DATE() function. You just tell it the cell addresses for the year, month and day (in that order)
=DATE(a2, b2, c2)

Situation 3: The data you got came with dates that are really just strings, such as "20200420" (year-month-day). You can use a combination of DATE() and string functions to convert it.
=DATE(LEFT(A6,4), MID(A6,5,2), MID(A6,7,2))

Weeknum returns the number that corresponds to the number of weeks that have elapsed since January 1.

=Weeknum(celladddress)

Worksheet: Time

One of the most common things journalists want to do with a date/time field is to calculate response times of local public safety units. To do this, you need to make sure to have full date/time fields for all the key time points you want to compare (i.e. time of 911 call, dispatch time, arrival time, cleared time). Be sure that these have dates for each time, as well, because calls that occur just before midnight might result in an arrival or cleared time occurring on a different date.

Even if you’re not doing response times, a useful formula you might need would be this one to strip the time portion off of a date/time field:

=TIME(HOUR(h4),MINUTE(h4),SECOND(h4))

The best approach for calculating a response time is to convert your time into seconds. Here are the steps you’ll need to do that. (use the worksheet called “TIME” to follow along):

This assumes that you have a date/time field (i.e. “3/31/2013 12:00 PM” or “3/31/2013 14:00”):

=TIME(HOUR(h4),MINUTE(h4),SECOND(h4))*86400

Note: 86400 is the number of seconds in a 24-hour period. So this answer is really representing the time as the number of seconds that have elapsed since midnight.

If you have response times with just a time—no date (i.e. “12:00 pm), then you can just multiply that by 86400.

To deal with calls that run across midnight (call received in p.m. and the arrival time is in a.m.), we need to be able to handle these differently than the other calls. So we need our formula to be able to check for that.

The simplest would be to have it look to see if the receive date is different than the arrive date. However, our fields have both date AND time. So it might help if we add new fields that just hold the dates.

So we’ll create “RECEIVE DATE” AND “ARRIVE DATE” fields and populate them using these formulas:

=DATE(YEAR(H4),MONTH(H4),DAY(H4))

=DATE(YEAR(I4),MONTH(I4),DAY(I4))

Note the “DATE” function used here requires you to put the year first, then month, then day. A little counter-intuitive .

Of course, if you’re feeling confident, you could build that date function into the formula below. It would just make a really long and complex formula.

Now we can calculate the response time – the difference between the receive time and the arrive time, and display our answer in minutes.

Here’s the formula:

=( IF(L4=M4, K4-J4, (86400-J4) + K4)) / 60

Here's what it's doing:

IF “receive date” and “arrive date” are equal, then

--“arrive time” minus “receive time”

If not true, then….

--86400 (seconds in a day) minus “receive time”, and then add the arrive time

Finally,

Divide the answer by 60 to convert it from seconds to minutes

This strange formula used in situations when the receive and arrive date/time fall on different dates puts the receive time and the arrive time into the same time frame to make it possible to subtract without getting a negative number.