Date & Time Calculation

[pfales]

I am having an issue with time and date calculations and I was hoping that you could help shed some light on the topic for me. Actually I believe I have the Date portion almost whooped but the time is still whooping me.

I will try my best to provide you with as much information as I can regarding this matter. The table I am utilizing has the following fields;



PK = TID
TDate = Start Date (Short)
TTime = Start Time (Short)
TFinish =Completion Date (Short)
TFTime = Completion Time (Short)

TDuration = Total number days
TDTime = Total numbers of hours, minutes

The date portion I have almost got whooped the only part I am trying to figure out there is how to get 6/09/2013 and 6/09/2013 to equal zero (may have unknowingly resolved this issue).
The time portion though is a completely different street fighting situation. Here is the data from my table to help give you an idea;

TDate	TTime	TFinish	TFTime	TDuration	TDTime
7/2/2013	9:15	7/2/2013	10:15	0	4.13310185185185E-02

It is my understanding that the first portion (TDTime Field before the decimal point) is supposed to indicate days, correct(?), everything after the decimal point is the time. How can I get this (the number in the TDTime field after the decimal point) to come out in hours and minutes? I have tried utilizing =DateDiff("h","m",[TFTime],[TTime]) and =DateDiff("h","m",[TFTime],[TTime])*24 (recommended in some ACCESS sites) but both do not provide the result (and most of the time I believe incorrect results) but not in the format that I am trying to obtain.

Any help would be greatly appreciated.


V/R
Pfales

[Perceptus]

MsgBox DateDiff("n", "9:15", "10:15")

should give you 60.

i found this in the F1 for Ms access. its also here http://msdn.microsoft.com/en-us/library/ms189794.aspx

[pbaldy]

Personally I would have 2 fields rather than 4, with the date and time combined. That makes your calculations simpler and handles times across midnight easier. Failing that, I'd combine them in your functions, like:

DateDiff("n", TFinish + TFTime, TDate + TTime)

I don't think your DateDiff() functions are valid, as I believe there's a single argument for the interval, not 2. Also, "m" is months; "n" is minutes.

[pfales]

Thank you both for your responses.

PBaldy,

[pfales]

My apology pbaldy, I sent the message before Iwas ready.
Your recommendation to utilize just the two fields makes sense, I will try both recommendations though.

[pbaldy]

No problem.