Hi.
I want to subtract times and add the results together; e.g. 3:24 pm minus 12:43 pm, or 3 hours 18 minutes plus 1 hour 54 minutes.
What is the best method to do this generally. Calculators & spreadsheet solutions are welcome in addition to doing it by hand. However, programming is wholly alien to me, so programming solutions won’t work.
Thanks much!
Excel knows all about time. Go type 3:18 in a cell, 1:54 in a cell, then add the two together. It does it.
It can do more complicated stuff, too, no programming knowledge needed. Just read the help files.
I tried that. MS help files are impenetrable to me. Will it also subtract times of the day as well? Do I just convert them to military time and do it that way? (Ah, good thinking…)
One technique I often use involves getting a scientific calculator with a [o ’ "] button (that’s the degree-of-arc sign, a single quote as the minutes-of-arc indicator, and a double quote as the seconds-of-arc sign.)
It’s not designed for doing calculations with hours minutes and seconds, but since minutes of arc are just 1/60th of degrees, and seconds of arc are just 1/60th of minutes, it works out the same as hours-minutes-seconds of time. For instance, to solve your two problems:
(I’ll just use here for pressing the arc button.)
15 24 - 12 43 = shift
that displays 2 o 41 o 0 , or two hours and 41 minutes
3 18 + 1 54 = shift ( 5 o 12 o 0 – five hours, twelve minutes)
It’s complicated to explain, but easy when you get the hang of it I think. The final ‘shift ’ in those sequences is to convert from an answer in decimal fraction, like 2.68333… to one in arc notation, which can be read as 2 hours 41 minutes.
If I’m understand 3:24 pm minus 12:43 pm, you’re trying to figure out how much time elapsed between each of these? That’s pretty straightforward. I’d convert the first time to 24-hour time, and get 15:24 - 12:43. So I convert 15:24 to 14:84 (borrowing 1 hour from the hours unit, and adding the borrowed 60 minutes to the minutes side.) Now you have 14:84 - 12:43, which easily works out to 2:41.
For 3:18 plus 1:54, just add the hours and minutes separately. You get 4:72. Subtract 60 from the minutes side, add 1 to the hour side, and you get 5:12. If you’re a little more clever, you can do this in one step less. 18+54=12, carry the one over to the hours side.
Does this make any sense?
Oh, and if the calculations span two consecutive days (say 11:32 pm and 3 a.m.), just add 24 to the time on the second day. Thus, to find out the time between 3 a.m. on Tuesday and 11:32 p.m. on Monday, you do 27:00 - 23:32 (or 26:60-23:32) and get 3:28.
Excel converts all times and dates to anumber. For dates, the number represents the number of days that have elapsed since 1/1/1900. For time, the number is a fraction, representing the the portion of the day that has passed since midnight (eg: midnight = 0, noon = .5). I amways enter time as nn:nn XM (where X is A or P). Once they are converted to numbers, the arithmetic is farily stright forward.
You can also see what number represents every date and time by changing the format of the cell (Format | Cells, select the ‘Number’ tab and select the ‘Number’ format)
The way to do it is similar to the way that you add and subtract regular numbers, with “borrowing” or “carrying” numbers between the hours and seconds.
For adding times together, you first add the minutes together. If that number is over 60, you subtract 60 from it and carry a 1 into adding the hours. Then, when you add the hours together, you see if that number is greater than 12. If it is, then you subtract 12 from it and change am to pm, or vice versa. If you were calculating a duration, then you carry the 1 into the days part. If you were just calculating a time of day, you can just drop the one.
For subtracting, you first subtract the minutes. If the top number is less than the bottom number, you first borrow an hour from top number, reducing that number by 1. Once you’ve borrowed 1, you then add 60 to the top number, and then do the subtraction. Then, you go over to the hours part and subtract those numbers. If the top number is less than the bottom number, then you add 12 and change am to pm or vice versa.
One thing I’d like to point out is the difference between a time of day and a duration. You can’t add two time of days together, if you add a time of day to a duration then you get a time of day, and if you add two durations together then you get another duration. Similarly, you can’t subtract a time of day from a duration, if you subtract a time of day from a time of day you get a duration, if you subtract a duration from another duration you get a duration, and if you subtract a duration from a time of day, you get a time of day.
For instance, let’s day that you want to find the time that is 4 hours, 25 minutes before 2:15pm.
2:15 pm
- 4:25 duration
-------------------
??:?? pm
1:75 pm
- 4:25 duration
-------------------
??:50 pm
13:75 am
- 4:25 duration
-------------------
9:50 am
So 4 hours and 25 minutes before 1:25 pm is 9:50 am.
Here’s a little freeware program that I use - Time Calculator Pro - it’s been very handy and simple:
Link