• calculate duration or difference in time

    From hamaradha hassan@21:1/5 to All on Wed Apr 27 02:31:52 2022
    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 )

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Philip Herlihy@21:1/5 to All on Wed Apr 27 15:43:28 2022
    In article <8dc82bfe-29e2-4847-ba7d-ed9b833414b0n@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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)