Calculating hours in Excel
July 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.
Please help, this is so silly and I've even watched YouTube videos which direct me to do exactly what I've already tried.
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.
Please help, this is so silly and I've even watched YouTube videos which direct me to do exactly what I've already tried.
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]
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]
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]
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]
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]
posted by madcaptenor at 1:30 PM on July 14, 2021 [1 favorite]
This thread is closed to new comments.
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]