What you could do is use another row to convert your timestamp into Unix time. Unix time is, simply speaking, the number of seconds that have elapsed since 1 January 1970, so the Unix time of an event will simply be a big number (as I’m writing this, it’s 1497087083, for instance). This website gives instructions on how to convert date and time into Unix time, or vice versa, in excel.
Once you have your start and end time for a given practice period in Unix time, you can easily calculate the period between a given start and a given end time by means of subtraction. That will give you the duration in seconds, so you should think of conversion into minutes (by dividing by 60) or hours (dividing by 3,600). You might also want to format the cells with the result so that there’s an appropriate rounding.
Once you have individual cells with the duration of each practice period, summing them up to subtotals and totals is straightforward.
The Excel time stamp is only the time. It’s separate from the date.
Somehow I’d have to combine the two cells into a combined date/time format. Then get a Unix date.
The rub is the need for a static date. Unchanging.
Open the spreadsheet. ctrl : and then ctrl shift : stamps the date and time into the cells.
It’s like the time clock at work.
something like =Today() changes every time the spreadsheet opens
I know dates and times are stored as a number. You should be able to subtract two dates and get the elapsed days in a number.
That’s the first thing I tried with the two time cells. I got some weird answer. I had to use the =Text to get the right answer.
Excel, but not Excel Online, offers a possibility to insert a static timestamp. And of course, manual entry of the time might also be acceptable.
If time and date are stored in different cells (and you never have a practice period that extends beyond midnight), then you can apply the timevalue function to the cell with the time instead of bothering with Unix time. In that case, since the timevalue generated by this function is the decimal value of the time within a day (0 being 12:00:00 noon, 0.25 being 6am, and so on), the difference between two time values would have to be multiplied by 24 to be converted into hours, or by 1,440 to be converted into minutes.
I tested Start and Ending time for Null. If Either is Null return a zero.
I wanted to return Null but the multiplication requires a numeric value.
I know 1440 is 24*60. I’m leaving the numbers to document what I’m doing.
In case I need to look at this formula again in a year or two.
this works and gives me a plain number that can be easily summed.
I copied it down 200 rows. I used format, custom ### to suppress the zero.
Tested it by entering a start and end time in random rows. It returned the correct value.
=(IF(D8="",0,IF(C8="",0,TEXT(D8-C8,“hh:mm”))) 2460)
I still have to figure out a running subtotal that resets each day.
Any ideas would be appreciated.
Looks like I rambled on too much last night. I had way too much coffee. I’m getting too old to stay up until dawn programming.
I guess a standard, row by row running total will work. I’ve used this trick with Sum many, many times. It works quite well. SUM ($A$1:A1) gets copied down the column. It adds the new row into A1. giving a running total
I really wanted a single subtotal. But it’s not worth wasting time figuring it out. Excel can be a PITA to mess with.
This is technically incorrect, and may be one of the reasons you are having trouble.
Excel stores date/time as a single number that is the number of days elapsed starting Jan 1, 1900 (that is, 1.0 is 0:00 on 1/1/1900). The integer portion gives the date, and the part after the decimal gives the time.
When you hit CTRL+: to enter the time, the date is 1/1/1900. If you hit CTRL+; to enter the date, the time is 0:00.
That’s because you are converting to text then using text in arithmetic. If you want time converted to minutes, you want
=(F1-C1)2460
This will work for any two times.
That formula is a little tangled. I would do this (you don’t need those outermost parentheses):
=IF(OR(D8=“”,C8=“”),0,(D8-C8)2460)
I can help with your overall problem of wanting to sum each day, if I can see your file. You can PM me and I will give you an email address. Also, I am a moderator on the Excel Forum https://www.excelforum.com/ , and you may attach files there (requires free registration).
I’d forgotten that Excel allows an OR condition. I’ll give that formula a try. It’s a cleaner approach then a nested IF.
I appreciate the offer of help. I’ll send you a PM.
I write spreadsheets at work. But only two or three a year. They are often very simple with very few formulas.
I always end up forgetting the Excel functions and syntax. It’s hard remembering a language I use infrequently. It would be different if I wrote spreadsheets every month.