CODEWITHSUNDEEP
excelexcel-formula
Sam
Sam

Reputation: 43

Getting minutes between different dates in excel

I'm trying to calculate the time between the dates , at the beginning the formula was working fine but I've noticed that it does not work when the date is different

For example , I have the following information on cell A1: 09/15/2016 10:00 AM On Cell B2 I have: 09/16/2016 10:00 AM

The formula is just B2-A1 but instead of giving me a result of 24 hours is just giving me 0 . I believe the formula is not recognizing that these are 2 different days and is just doing 10-10

Any idea how to fix this ?

Upvotes: 0

Views: 1296

Answers (2)

Daniel Nelson
Daniel Nelson

Reputation: 51

I was able to get the result 24 by setting a custom format of [h] (you will have to type it into the 'Type:' box) on cell C1 while using the formula =B1-A1

Excel Reference

'Format Cells' view

The problem with just using =B1-A1 is that if either or both of those cells is not populated then you will get weird numbers in C1. You may want to make C1 display as a blank cell unless both boxes are populated, try something like this =IF(OR(ISBLANK(A1),ISBLANK(B1)),"",B1-A1)

The reason for the weird numbers is that Excel calculates time based on a predefined decimal system that indexes time starting at like 1/1/1900 or something like that. So when manipulating or calculating time, that is something that you always have to keep in the back of your mind.

Hope this helps.

Upvotes: 0

Allan
Allan

Reputation: 49

Formation the destination cell to will do but since you have date and time combined it will show as 1 calendar day difference 0 only means that 12 am after the 1 day difference, I know it does not make any sense but its Excel...

If I was you, on column A, I would add the date, and on Column B, the time. then just work with the time, as both combined can be tricky

Don't forget to format your cells!! (right click>Format Cells>Time>3/14/12 1:30 PM)

Upvotes: 0

Related Questions