As part of my day job as a building scientist, I often need to work with and normalize building temperature logs which are provided in CSV / Excel format. Part of the requirement comes from the fact that I am trying to understand how the building is operating during business hours but the data provided is often in 5 – 30 minute intervals which include 365 days of data. Since the building heating and cooling systems are reduced on the weekend as there is not typical occupancy, this data needs to be excluded. In addition, often there is a need to normalize the timestamps to the nearest 5 minute interval, as the individual temperature sensors may be at a slightly different starting time (e.g. Sensor 1 starts 1:05, Sensor 2 tarts 1:06 and Sensor 3 starts 1:07).

Because of this requirement (and the need to continue to use Excel) I have a few useful tips and tricks I thought might be useful to share with respect to time and date functions. Of course, this does not cover every time and date function but it does give a bit of an idea on how to deal with the above.

How is Date & Time stored in excel?

Time and date values are interesting in excel, as they are actually stored as a serial number in the format of ddddd.ttttt where the days integer represents the number of days since 1900-Jan-0 and the time represents a fractional portion of the 24 hour day. This is important as this format is not a text field, so if you put a text value of a date (e.g. “January 1st, 2020) excel will likely recognize this as a text value and any date or time related formula will not work. Its not to say that dates are shown as a serial, just they are stored in a serial format in the backend so if you have text values they need to be converted to serial.

How can i convert from text values to date/time?

Converting to serial number is very easy within excel, usually you can do this in two different ways, the first is to use =VALUE(CELL) which will convert any text string that looks like a number into a number. This should typically work with date and time values so is the first recommended option. If the text string isn't in a format recognized by excel it will usually return #VALUE!

The other option is to use =DATEVALUE(CELL) which only converts a text value that looks like a date to a serial number, so it will leave out the time portion of the string. This may or may not be useful for your use case.

If you have periods (.) between your dates for example 1.1.2020 then VALUE and DATEVALUE will likely not work and you may need to use find and replace to change these or possibly the SUBSTITUTE function.

How can i tell if a date falls on a weekday and within business hours?

There are a few different functions that I tend to use to work this out which get put into an IF statement with an AND clause. Before I run through how we do that lets look at some of the functions that I usually use. The first of which is WEEKDAY.

=WEEKDAY(CELL,RETURN)

The above formula takes a date (in this case CELL), and also has an option to select how your week usually runs. In my case I am using Monday – Sunday as my week so I set option 2 for RETURN. The function will then return a number which represents the number of the day of the week. In the case of =WEEKDAY(“1/11/2020”,2) it would return the number 7 as today is Sunday (Australian date format). We could use this in an IF statement to say:

=IF(WEEKDAY(CELL,2)<6,value_if_true,value_if_false)

The issue with the above is that we also need to consider the time, as the above would only tell is if the day was a working day but would not consider if the time was within business hours. To work out the time I personally think the easiest way to do this is to use the HOUR function.

=HOUR(CELL)

The above formula returns a number based on the hour of the timestamp, if I have the following date and time “2/11/2020 5:01:01 PM” in cell A14 and I call the formula =HOUR(A14) it will return 17 as in 24 hour time, the hour is 17 (5pm = 17). There is another version of this formula called MINUTE(CELL) which returns the number of minutes, and one called SECOND(CELL) which returns the number of seconds from the timestamp in the cell.

In order to work out if the date and time stamp is between business hours,  we need to use an IF statement however, we also need to determine if multiple criteria are being met, for example is the day < 6 (Monday – Friday), is the time equal to or after 9 AM, and is the time equal to or before 5 PM. Because the IF statement is only taking one logical test, we need to use an AND statement.

The AND function is a logical function that returns either TRUE or FALSE based on the criteria being queried. The AND function can take up to 30 conditions in total, and will return TRUE if all conditions are true, or FALSE if any or all conditions are false.

=AND(condition_one,condition_two)

In the case of our query, we want to work out is the date between Monday – Friday, and is the time after 8 AM (so 9 AM or 9 in 24 hour time) and before 5PM (17 in 24 hour time). To do this we could use the below AND:

AND(WEEKDAY(CELL, 2) < 6, HOUR(CELL) > 8, HOUR(CELL) < 17)

If we fed this into an if statement like the below, we could then return “Working” or “Non-Working”:

=IF(AND(WEEKDAY(CELL, 2) < 6, HOUR(CELL) > 8, HOUR(CELL) < 17), “WORKING”, “NON-WORKING”)

There is an issue with the above formula though, since we are only looking to see if the time is < 17, technically the formula will say it is not working if the time is 5:00 PM on the dot (so 4:59PM becomes the last working time in essence). If you need the timestamps to include 5 PM as business hours I generally put a nested IF statement in the NON-WORKING section like this

IF(AND(WEEKDAY(CELL, 2) < 6, HOUR(CELL)=17, MINUTE(CELL)=0), “WORKING”, “NON WORKING”)

The above IF statement would get run if the other criteria are not met, if it is a weekday, and the time is 5:00 PM it will return WORKING otherwise it will return not working. The full Formula is below;

=IF(AND(WEEKDAY(CELL, 2) < 6, HOUR(CELL) > 7, HOUR(CELL) < 17), “WORKING”, IF(AND(WEEKDAY(CELL, 2) < 6, HOUR(CELL)=17, MINUTE(CELL)=0), “WORKING”, “NON WORKING”))

With the above, I usually create a new column and put this formula in it, I can then filter by all entries which are “WORKING” and process the data however I need to process it. The above assumes business hours are Monday – Friday, 9 AM – 5 PM. If your business hours are different you will need to change this.

How can I round time & date values?

So far with the above, we have looked at determining if a date-time stamp falls between Monday – Friday, 9 AM – 5 PM however often as I mentioned the timestamps can be all over the place, as a result to get normalized data there is often a need to round the timestamp. To achieve this I typically use the MROUND function. This function returns a number which is rounded to the nearest multiple. It should be noted that it rounds up, away from zero if the remainder of the dividing number multiple is greater than or equal to half the value of the multiple.

The structure of the MROUND function is shown below;

=MROUND(number, multiple)

In this case, the number we are rounding is the CELL and the multiple we want to round to is “0:05″ which means we want 5 minute intervals, if you wanted more or less e.g. 10 minutes you would change that accordingly. Using the formula =MROUND(CELL,”0:05”) if the CELL has a value of 17:00, the time will be 17:00 returned however, if the value of the cell is 17:04 it will return 17:05. This is useful because it means we can now start to normalize all the values to the nearest 5 minutes.