# Calculating hours in ExcelJuly 14, 2021 11:56 AM   Subscribe

I cannot get a time calculation to work in Excel (Microsoft 365) and it's driving me a little batty.

I am trying to log how much time I spend per day on a task. I want to do this in Excel. I have Column A and Column B formatted as date. I enter 1:00 PM in Column A and 3:00 PM in Column B. Then in Column C I enter the formula "=B1-A1". The output in Column C is 2:00 AM. Okay, so it got the 2 hours part right, but why is it displaying as a time?? If I try to force Column C to be formatted as a number with two decimal places, the output reads as 0.08 which makes no sense to me.

posted by Bebo to Technology (6 answers total) 1 user marked this as a favorite

Best answer: Excel represents time internally using days as the units. "2" as in 2:00, divided by 24, is 0.083333 - or 0.08 days, to two DP.

In other words, multiply column C by 24 to get your answer in hours.
posted by buxtonbluecat at 11:59 AM on July 14, 2021 [3 favorites]

Best answer: I believe your c column is still formatted as HH:MM AM/PM. If you change the format (in C column only) to display HH:MM only, it will stop appending the am/pm.
posted by Think_Long at 11:59 AM on July 14, 2021 [2 favorites]

If you right-click and select Format cells, under custom, you should be able to select h:mm instead of h:mm AM/PM
posted by TORunner at 12:00 PM on July 14, 2021 [1 favorite]

You'll need to multiply the result (i.e., 0.8) by 24.
posted by grateful at 12:00 PM on July 14, 2021 [1 favorite]

Response by poster: Thanks, all! Either keeping it numeric but multiplying by 24, or keeping it as time but removing the AM/PM both work. Appreciate the quick responses.
posted by Bebo at 12:03 PM on July 14, 2021 [1 favorite]

Also, I could imagine you might want it in minutes (either now or sometime later); in that case keep it numeric but multiply by 1440 (24*60, the number of minutes in a day)
posted by madcaptenor at 1:30 PM on July 14, 2021 [1 favorite]

« Older I lost it. Now I want it to stay lost.   |   Please recommend some recent social... Newer »