Functions for dates and times

miIf you go into the “Date & Time” category in the function selection menu of any spreadsheet program you’ll find a lot of options, but there’s only a few I use on a regular basis. These functions are consistent over pretty much all programs, with the exception that Open Office uses semicolons (;) instead of commas (,).

=today(): This function returns the number for the current date, and will usually cause the cell to be automatically formatted as a date. That formatting can be overridden if you want it to appear in a different way. I use this function a lot in my budgeting, and also when assigning member numbers to people since my employer’s member numbers start with the last two digits of the year. (Yes, I know that technically that’s bad programming practice, and it’s what led to the Y2K bug. But since in order for two current members to wind up with the same number one of them would need to be at least 130 years old, I’m not too concerned.)

=now(): This function returns the number for the current date and time. If you just want the time, you need to do =now()-today().

=date(year,month,day): This function returns the number which, when formatted, will appear as the specified date.

=time(hour,minute,second): This function returns the number which, when formatted, will appear as the specified time.

=day(date): This function takes a number, and returns the day of the month of the date it represents. For example, 1 is 12/31/1899. So day(1) = 31.

=month(date): This function takes a number, and returns the month of the date it represents. Extending the previous example, month(1) = 12.

=year(date): You can probably guess by now what this function does – it takes a number and returns the year of the date it represents. Year(1)=1899.

=hour(time): This function takes a number and returns the hour of the time it represents. 0.5 is halfway through the day, so =hour(.5)=12.

=minute(time): This function takes a number and returns the minute of the time it represents. If cell A1 contained 1:30, then =minute(a1) would be 30.

=second(time): This is exactly the same as minute and hour, except it returns the second. I can’t say that I’ve ever actually used this function, but I’m including it here for completeness’ sake.

=mod(number): This is not actually a date/time function, but I use it extensively when working with dates and times, so I want to include it here. Mod is short for modulo, which is essentially a way to get the remainder after dividing. Some of you may remember that it’s also commonly denoted with a %. For example, 100/3 = 33 1/3, so 100%3 or mod(100,3) is 1. Likewise, mod(2012,100)=12. I often use it like that to extract the last two digits of a date. Similarly, while now()-today() might be great for getting the current time, if you have a recorded date-time combo, then there’s basically two ways to get the time out of it. The most obvious way would be to do =time(hour(datetime),minute(datetime),second(datetime)), and this would extract the hour, the minute, and the second from the date-time combo, and then recombine them into an actual time. Alternatively, since the dates are the part in front of the decimal, and the times are the parts after it, you could do =mod(datetime,1). Much, much simpler, and therefore less prone to errors.

I often use these functions in conjunction with each other. For example, if I’m recording something on a monthly basis, rather than typing 1/1/2012 in A1, and then 2/1/2012 in B1, I instead put the following formula in B1: =date(year(A1), month(A1)+1, 1). That way it takes the month and year from the previous cell, adds one month, sets the day of the month to 1, and then returns the resulting date. Or, if it’s something that occurs on the last day, rather than the first, I might do =date(year(A1), month(A1)+2,0). Since the first day of the month is 1, the zeroth day is one less, so it’s the last day of the previous month. This is also why it’s month(a1)+2, not month(a1)+1. Just think of it in terms of two steps forward, and one step back.

I also use these functions to generate statistics. For example, look at the countif example. Although I didn’t explain it at the time, in column D of the “Library Instruction” worksheet I used month() to extract the month that the class was taught. This allowed me to create monthly summaries in the table on the “Summary Tables” worksheet.

So, linking this back, with last week’s discussion of how to extract the day, month, and year from text-format dates, these functions give you a way to rebuild them into actual dates that will be recognized by the spreadsheet.