Time minus Time in Excel

[Loneless]

Hi all, first time poster here. I'm trying to get the total hours & minutes based off of a time minus time in Excel. For example, let's say I clock into work at 11:50 PM, and clock out at 8:03 AM, I would like a formula that will show how many hours and minutes I worked (which should be 8 hours and 13 minutes).

Here's a picture from my excel workbook:

I want to basically subtract B3 from B2 and get the hours/minutes. Any help is much appreciated, thank you !

 

[hallingh]

A simple subtraction formula will work. Just format the cells in the column B to be in Time format "8:30 PM" and the cells in Column C to be in Time format "8:30:00."

Then when you put the subtraction formula in column c it will show you the difference between the two cells in Hours Minutes and Seconds.

Hope this helps.

Hank

 

[Loneless]

Hi Hank, thanks for the reply.

When I try =B3-B2 I get ###########, when I switch it to =B2-B3 I get 15:50:00. The cells should be formatted correctly (B column set to Time - h:mm PM, and C colum Time - hh:mm:ss).

 

[hallingh]

Ah, I didn't see that you're subtracting an AM time from a PM time. I'm assuming that's the issue, but I'm not positive. I can't look into it right now, but I should be able to in the next 30-45 mins. If no one replies by then, I'll see what I can do.

 

[asking]

In cell C3 and fill down this formula but be sure to format col C in time format without the AM or PM

=IF(B3>B2,"",IF(B3<B2,B3+1,B3)-B2)

Cheers

 

[asking]

Did not Come out as typed I will try again

In cell C3 and fill down this formula but be sure to format col C in time format without the AM or PM

=IF(B3>B2,"",IF(B3Less thanB2,B3+1,B3)-B2)
the less than symbol won't Type so needs inserting where less than is.

 

[markmzz]

Ah, I didn't see that you're subtracting an AM time from a PM time. I'm assuming that's the issue, but I'm not positive. I can't look into it right now, but I should be able to in the next 30-45 mins. If no one replies by then, I'll see what I can do.

Hallingh,

Or try this in C3:

=IF(A3<>A2,MOD(B3-B2,1),"")

Markmzz

 

[Loneless]

Thanks guys, that formula is working perfect!!

 

[AbelLancet]

I have a similar question. I want to be able to enter a value of time '16:32', and have it return a True or False value if the specified time from now() lands between 2:00 - 5:00.

I really appreciate all suggestions.
Thanks,
Abel

 

[mikerickson]

For the OP , try =(A3+B3)-(A2+B2)