Date difference in minutes

marafaka · Guest

I can't find an easy way to calculate difference between two dates in hours, minutes or seconds. Should I write my own function somehow or what?

Thanks.

JohnV · Posted: Sun Nov 16, 2003 12:43 pm Post subject:

Try this.

If you format your date cells A1, the start date, and A2, the end date, with one of the date formats and then format the difference cell, A3, as [HH] or [MM] or [SS] and put the formula =A2-A1 in it, then you should get the result in hours, minutes or seconds . The square brackets do the magic.

(I hope this works for you because this is my 500th post and I have been designated as a "Super User". I get something right on occasion and I sure would like this is one of them!)

marafaka · Guest

Thanks JohnV, exactly what I was looking for.

dfrench · Posted: Mon Nov 17, 2003 12:57 am Post subject:

I may be being a little too pedantic, as John provided exactly what was required, but the difference formatted in the way described will only show the fraction of a day ie will provide a time of less that 24hrs for "HH" or "HH:MM:SS" regardless of the number of days between start and end. With simple dates (no time) in a1 & a2 , a3 will always show as zero although it actually stores an integer.
Failing the existence of a differnce function (and there isn't one for hours minutes orseconds) any arithmetic can be performed on dates and times with the knowledge that they are stored as decimal parts of a day so multiplying the difference by 24 gets the answer in hours and will display correctly if formatted as a number

JohnV · Posted: Mon Nov 17, 2003 8:30 pm Post subject:

Hi David,

I'm not sure whether I agree or disagree with what you've said so I'll let you be the judge. I agree that if cell A3 is formated “HH:MM:SS” that the difference between two simple dates will show as “00:00:00” but the appropriate value you speak of will be stored in A3. However, if A3 is formated “[HH]:MM:SS” then the difference will show hours, like “24:00;00” while the same appropriate value will be stored. The following is what you get using the format where HH is between square brackets:

dfrench · Posted: Tue Nov 18, 2003 2:03 am Post subject:

You are perfectly right, I totally missed the magic of the square brackets. The documentation is pretty sparse too. Always learning with this forum but I think I will give that format a miss.
In my defence, I spend a lot of time with the calculation side of spreadsheets and try to keep it so that what I see is what I have got. When the next user comes along and starts calculating with that "number of hours" ... ugh!

JohnV · Posted: Tue Nov 18, 2003 11:40 am Post subject:

I thought you could have missed the square bracket part but wasn't sure.

Last night in the wee hours I went searching in the macros section for code to get the value from a Form text box. Not to my surprise, I found yours and was able to convert it to my needs. Thanks, I'm still a lost ball in the high weeds when it comes to the API!