Counting difference in hours between 2 dates and times

  • Thread starter Thread starter Mikaela
  • Start date Start date
M

Mikaela

Hello,

How can I calculate the difference in time between two dates and times.

The date and time is not in one box but in separate.
Ex. 11.8.2003 9:00 -> 12.8.2003 10:52 ->25,52 ->26h

The dates and times are stored in separate excel squares.

I would need to have the time in hours (full hours)
ex. 9,5 -> 10h
154,20-> 154h
 
"Mikaela",

Firstly, I believe you need to get rid of the periods in the dates. CTRL+H,
Find What = ".", Replace With = "/" or "-" (all of these without the
quotes).

Secondly, let's say your sample dates are in A1 and B1. In C1, enter this
formula -

=MROUND(B1-A1,$D$1)

In D1, enter 1:00 (1 hour, this is your value to round to).

You'll need to custom format C1 (Format -- Cells -- Number tab) as [h]:mm.

Finally, for the MROUND function to work, you'll need the Analysis ToolPak
installed (check via Tools -- Add-Ins). And you may need to AutoFit your
columns to display stuff correctly (ie: not ########).

HTH,
Andy
 
Back
Top