Calculating days & Time

[siapliw]

Hi,


I tried different datediff formulas & could not succeed to get the appropriate result.
I want the result in Days, hours & minutes.

I tried the following code, which gives the result in minutes.
=DateDiff("n",[2IPPVstartDate]+[2IPPVstartTime],[2IPPVfinisDate]+[2IPPVfinishTime])\60 & Format(DateDiff("n",[2IPPVstartDate]+[2IPPVstartTime],[2IPPVfinishDate]+[2IPPVfinishTime]) Mod 60,"\:00")
Please help.

[GeekInOhio]

This looks promising:

http://msdn.microsoft.com/en-us/library/aa227535(v=vs.60).aspx

[Missinglinq]

The very clever Diff2Dates hack by Doug Steele and Graham Seach, which can be found on the Access MVP site at

http://www.accessmvp.com/djsteele/Diff2Dates.html

makes it easy to do the type of calculations you want here, as well as others, using the Access native Datediff() Function. It allows you to get an elasped time in a number of different formats, such as Days/Hours/Minutes or Years/Months/Days, etc, but the underlying logic is really simple.

You calculate the difference between to points in time, in the lowest common denominator you're interested in (i.e. the smallest unit of time you want to report, in this case Hours) and then parse it out:

Hours = DateDiff("h", [StartDateTime], [EndDateTime])

so

Days = DateDiff("h", [StartDateTime], [EndDateTime])\24

and

HoursLeft = DateDiff("h", [StartDateTime], [EndDateTime]) Mod 24

Then to display it in the format 00:00, as you originally requested, you could use a Control Source of

=[Hours] \ 24 & Format([Hours] Mod 24, "\:00")

Or

=[Days] & Format([HoursLeft], "\:00")

in a Field that is defined as Text, to accommodate the non-numerical characters.

Linq ;0)>

[siapliw]

Is it possible to build 3 arguments here? What I want is D:MM:HH.
DateDiff("h", [StartDateTime], [EndDateTime])\24 & Format([Hours] Mod 24, "\:00") gives MM:HH result
What I'm missing?
Cheers.

[GeekInOhio]

I'd just use this, but I tend to get a bit lazy with these things.

http://msdn.microsoft.com/en-us/library/aa227535(v=vs.60).aspx

[Missinglinq]

...What I want is D:MM:HH.

Sorry for taking so long to get back to you; in the middle of getting a daughter married and lost track of some threads!

Assumming that D:MM:HH is supposed to be D:HH:MM or Days:Hours:Minutes, this works for me:

DateDiff("n", [StartDateTime], [EndDateTime]) \ 1440 & ":" & (DateDiff("n", [StartDateTime], [EndDateTime]) Mod 1440) \ 60 & ":" & DateDiff("n", [StartDateTime], [EndDateTime]) Mod 60

I've inserted the Field Names you gave in your last post.

Linq ;0)>