Is there an Excel formula for calculating a length of time and converting it to decimals?

If you are keeping time to the quarter hour, then the decimals are really decimals: i.e., hundredths of an hour. So you don’t need a formula. It’s simple subtraction, the same as you’d use if it were money.

All you have to do to compute time after midnight is to add 12.00 to your ending time. 3:30 a.m. = 15.50. Subtract 11.75 from 15.50.

This is exactly how I always kept my time sheets. As long as you stick to quarter hours and use decimals, those numbers are great tools for several work-related purposes.

I hate using Excel for time, so this is a guess, based on a couple of minutes’ fiddling.

Format A1 and B1 as Time.
Format C1 as Number (2 decimal places)

In A1 and B1, enter your times, including AM or PM. In C1, enter =(B1-A1)*24

For this example, it returns 6.50 in C1. You would have to deal with remembering that (for example) 6.25 actually means 6 hours, 15 minutes.

This is the simplest thing I can think of. There will probably be a way to write a short macro to return quarter hours, if you’d prefer that.

Great question. I’m going to get on my computer in a little bit, and see if I can figure it out. Just thinking it through, you need to convert the times you plug in to decimal form, before the calculation is done I think. You could have a start time column for am and another for pm, same for end times, so the computer automatically adds 12 where necessary. I’ll be back…

I never have figured out how to get it to compute time!

try this:
=ROUND((B1-A1)*24/0,25;0)*0,25
make sure that a1 and b1 are formatted as a time, and the cell with this formula formatted as a normal number.

Ok, I have a solution for the late night -> early morning hours:

(Format Time) A1: start time
(Format Time) B1: end time
(Format Time) C1: =IF(A1<B1,B1-A1,IF(A1>B1,12-B1+A1))
(Format Number) D1: =(C1-INT(C1))*24 (format Number)

In C1, you are checking to see whether the end time is earlier or later than the start time, and calculating the difference accordingly:

If the end time is later than the start time, it calculates (end time – start time).
If the end time is earlier than the start time, it calculates (12:00 – start time) + (0 + end time).

In D1, you are converting the answer in C1 into decimal form. I tried combining these two steps, but couldn’t make that work.

Bah! There are still problems with it… it will return different results depending on whether the start time was morning or after noon. Have I mentioned that I hate the way Excel deals with time?

Ok, I refined the formula a bit. Now it should handle the second time being smaller than the first time and interpret the second time as being in the next day.
=ROUND((IF(B1<A1;(24-A1+B1)-23;(B1-A1)))*24/0,25;0)*0,25

Simple subtraction. Pretend you’re handling dollars and quarters in two little piles. When you have to take away more quarters than you have in the quarters pile, you convert a dollar bill to quarters and then subtract.

Put another way, you use a 24-hour clock instead of a 12-hour clock so the intervals are easier to compute.

excel always uses the 24 hour scale internally, even if you enter it in the am/pm format.

@ragingloli That isn’t working for me. It keeps telling me there is an error in the formula. I copy/pasted it and everything! Excel is gonna’ drive me crazy.

@Jeruba I realize there are work-arounds, but I’m really looking for the spreadsheet to do all the thinking for me. I’m keeping track of a lot of different time periods these days, and I’m lazy.

@augustlan replace the commas with periods.

@ragingloli that formula has several problems on American versions of Excel, starting with the fact that the semicolons have to be replaced with commas; there’s a #DIV/0 error; there are too many arguments to the IF function, and as you noted the comma has to be replaced with a decimal point.

EDIT: My mistake; I hadn’t converted one of the commas. This works:
=ROUND(( IF( B1 < A1, (24 – A1 + B1) – 23, ( B1 – A1))) * 24/0.25, 0) * 0.25

Aside from that, there is an easy enough formula as long as all of the time entries are for “the same day”. Here’s a pretty simple formula:

=( MAX( A1, B1) – MIN( A1, B1)) * 24

This will subtract the smaller of A1 or B1 from the bigger (later) value – in the same 24-hour day – and convert that to “hours” when you multiply by 24.

The reason is that every time value in Excel is actually a “date” value, in terms of the decimal value of hours in a day. That is, “0.5” is 12:00 PM (Noon) and 1 = 12:00 AM (Midnight). So any time you enter, say, 8:00 AM or 0800 as a time value, it’s actually stored in Excel as a fraction of a 24-hour day. (You don’t have to understand this paragraph for the function to work.)

If there is any possibility that your two times in A1 and B1 span more than one day, then I’m afraid you may need a macro to do this. For example, if A1 is on July 2 but B2 is on July 10, you have to account for the number of days as well. And if A1 and B1 are in different months, or worse yet, in different years, you have account for year and month (perhaps even century) to do the job right.

I can’t help but think that someone has already done this. Search the Excel forums and see if your solution is already out there. No need to reinvent the wheel.

@CWOTUS That isn’t working for me, either.

@2davidc8 It’s not the end of the world if I can’t get it to cross over two days. I’ll just end one row at midnight, and start the next with midnight.

@CWOTUS That won’t work either… working from 2pm to 3pm is not the same number of hours as working from 3pm to 2pm. @augustlan is specifically looking for a formula that will span two days.

Well, if you’re spanning days, weeks or months, then there is a very simple function:

=DATEDIF( Later date, Earlier date, “d”) will give you # days between two dates. Or “m” will give # months, and “y” will give # years.

@glacial
You’re right, obviously.

@CWOTUS Yes, after you subtract out the years, months and days, you have to deal with the remainder (which is less than one day). Then you convert the number of seconds into hours, minutes and seconds. Then you round off the minutes and seconds to the nearest quarter hour, which is what I think @augustlan wants. All this can probably be done in a macro. But as I said, I’m pretty sure someone has already done this.

I figured out my mistake!! Use this for C1 instead of the line I gave above (and use the other 3 lines, obviously). It works for any combination of time of day, and it spans two days. The only thing that will cause it not to work is if the period is greater than 24hrs.

(Format Time) C1: =IF(A1<B1,B1-A1,IF(A1>B1,24-A1+B1))

@glacial Yay! That worked! Thank you so, so much. :D

Thanks to everyone else who tried, too. I appreciate all your brain power!