calculate duration or difference in time

[hamaradha hassan]

if in A2 time is 17:09 parking somewhere, and B2 is picking someone at 17:21 what will be the formula to find duration if A2=0, B2>0 the , C2=0, if A2=17:09, B2=17:21 C2=B2-A2 so what is formula for thin in excel sheet (C2 is duration )

[Philip Herlihy]

In article <8dc82bfe-29e2-4847...@googlegroups.com>, hamaradha
hassan wrote...

>
> if in A2 time is 17:09 parking somewhere, and B2 is picking someone at 17:21 what will be the formula to find duration if A2=0, B2>0 the , C2=0, if A2=17:09, B2=17:21 C2=B2-A2 so what is formula for thin in excel sheet (C2 is duration )

Tricker than it first appears!

It's invariably best to store dates/times in the way Excel "expects", which
means that if you enter a valid date then Excel will store it as a "datevalue",
which is a numeric code. Those values can be formatted to display in a choice
of date/time formats, and can be added and subtracted. If you format the
result as a date or time then you'll see the result you'd expect.

One gotcha in this is that if you format the result of your subtraction as
"time" (remember CTRL+1 gets you into the Format options screen in Excel) then
subtracting cell values displaying as "27/04/2022 17:09" from "27/04/2022
17:21" will give you a value which shows "00:12:00" when formatted as time, but
it shows the same thing if the value you're subtracting is one or more days
earlier. (If the other way round you'll get a row of '#' characters indicating
a negative result!) As ever, you have to guard against data entry errors - and
those are easily made.

So you might want to check the times refer to the same date; you can do this by
checking the result of DAYS(first-date, second-date) is zero before proceeding
to the subtraction.

Another problem is that the output of the simple subtraction, even when
formatted as "time" (e.g. 00:12:00) isn't a value you can easily work with.
The value in the relevant cell isn't the integer '12' but a timevalue which
when formatted as "time" comes up with the string "00:12:00". Try adding one
to it via a further cell to see what I mean!

So you might consider using the MINUTE() function, which returns the "minute-
value" of the date/time value stored in the cell you reference. That does
return an integer which you can use in further calculations (like deriving
charges).
Formula: = MINUTE(B2)-MINUTE(A2) will return "12" for the values you quote.
But that's regardless of date, and also regardless of the hour-value! So a
stay from 17:09 to 19:05 is going to be a problem! There is an HOUR() function
which you can use as well to get back numeric values which you can use as the
basis of further calculations. (Of course you can simply put the simple
subtraction in a cell formatted to show "time" and use that to verify the
calculation is correct - in this example it'll show a string "02:12:00".

The most general case is to anticipate someone leaving their car there for
days. So you're maybe leaning towards adding:
*) the result of the DAYS() formula based on those cells
+
*) the difference between HOUR(later cell) and HOUR (earlier cell)
+
*) the difference between MINUTE(later cell) and MINUTE (earlier cell)

But if you think about it, even that's not enough. What if they arrive at
17:09 on the 29th, and leave at 15:03 on the 2nd?

I think that sets out the various bear-traps! So here's the answer:
https://www.youtube.com/watch?v=OSKNu8I5Rx0 (adapt to your particular needs).

If your particular situation does require logical testing (IF) then have a look
at the marvellous Leila Gharai on the subject:
https://www.youtube.com/watch?v=KkTaQ5OjAGc

Hope that helps. Fun thinking it through anyway! (I must be a sad case...)

--

Phil, London