Tracking days, hours, minutes, seconds with Excel

When testing different projects, sometimes it is helpful to know how long something has been running.  I recently ran into this while doing some final testing for a countdown timer for a client. For this particular design, I needed to test a circuit once a day for about a month. Unfortunately, I could not automate the test – I had to do it manually. Time to bust out a table.

 Microsoft Excel is usually my go-to application when I need to tabulate and collate some test information.   Excel is incredibly powerful and has a myriad of built-in functionality and programmability.  I am not an expert in Excel, but I can usually wrestle it into submission and get it what I want it to do.  For this particular testing procedure, I came up with a formula that may be of use to some of you out there.  Excel has some fairly powerful arithmetic capabilities built in.  Unfortunately, there appears to be a split between the Years, Months, Days operations and the Hours, Minutes, Seconds operations.  There are not any built in functions that allow the two types of operations.  I needed to roll my own.

I was going to run the test unit for 24 days.  I wanted to enter in a time and have the Excel sheet show me how much time would be left on the unit.  Here is the spreadsheet I came up with.  I will describe how each of the cells is derived along with some background.

image

A note on Microsoft Excel Time format.  Microsoft has a fairly unique time format for Excel.  See this link for information how it works.

The basic test was this: I put 24 days on my test unit and had it start count down.  Every day I would check to ensure that the test unit was counting down correctly.  Cell B3 shows the start of the test.  Cell C4 shows that 24 days are on the test unit.  Column B (Starting at Row 7) shows when the time was checked.  I could simply enter in the time and date in column B and the spreadsheet would take care of the rest.  After entered the time  into column B, Excel converted it to the Excel format.  I did that by simply setting the cell format to “General”.

Column D simply subtracts the converted time in column C from the converted start time.  So in D7, the “0.02083333” represents a half hour.  Column E shows what the time on the unit should be in the Excel format.  So in E7 the “23.97916667” represents 23 days, 23 hours, 30 minutes in Excel format.  Unfortunately, that is not easy to read.  Column F is where the magic happens.  The value in column F is given in the format of DAYS:HOURS:MINUTES:SECONDS.  So F7 is 23 Days, 23 Hours, 30 minutes, and the seconds values I don’t care about.  The formula to convert the E column value to the F value is given below:

=INT(E7)&":"& INT(MOD(E7,INT(E7))*24)&":" &  MINUTE(E7) & ":XX"

Here is how it works:

DAYS = INT(E7) – this is simply the integer value in E7, which in this case is “23”

HOURS = INT(MOD(E7,INT(E7))*24) – the “MOD” function, takes the decimal portion of E7.  By multiplying it by 24 (number of hours in a day), it gives the number of hours, in this case “23”.

MINUTES = MINUTE(E7) – this is a built in function that Excel has that tells how many minutes are in a cell, after all of the hours have been taken out.

With the help from Google. I was able to get a quick a dirty Excel spreadsheet to do what I needed.  Hopefully it will be of use to you.

Arduino Clock

bm005 real time clock

An Arduino clock and calendar is easy to build using the BM005 clock module.  The heart of the module is Microchip’s MCP79400 I2C RTCC.  The MCP79400 has a number of cool features, including leap year compensation, battery back-up (with an external battery), time/date storage on power failure, 12 or 24 hour format, and even some extra RAM if you need it.  Our BM005 puts it all in an easy to use module for a retail price of $12 (BM005 datasheet).

[Read more…]