Computing minutes, days and years

  • Thread starter Thread starter PeterM
  • Start date Start date
P

PeterM

I have the following on a form in AC2003.
txAlarmTime is in general format "mm/dd/yyyy hh:mm:ss ampm"

Dim tmpMinuteDifference, tmpHourDifference, tmpDayDifference,
tmpYearDifference As Variant

tmpMinuteDifference = DateDiff("n", Format(Me.AP_Start_Time,
"hh:mm:ss"), Format(Me.txAlarmTime, "hh:mm:ss"))
MsgBox tmpMinuteDifference & " minutes"

tmpHourDifference = DateDiff("h", Format(Me.AP_Start_Time, "hh:mm:ss"),
Format(Me.txAlarmTime, "hh:mm:ss"))
MsgBox tmpHourDifference & " hours"

tmpDayDifference = DateDiff("d", Format(Me.AP_Start_Date, "mm/dd/yyyy"),
Format(Me.txAlarmTime, "mm/dd/yyyy"))
MsgBox tmpDayDifference & " days"

tmpYearDifference = DateDiff("yyyy", Format(Me.AP_Start_Date,
"mm/dd/yyyy"), Format(Me.txAlarmTime, "mm/dd/yyyy"))
MsgBox tmpYearDifference & " years"

if AP_State_Date = 1/6/2010 and txAlarmTime = "1/6/2010 08:45:00"
then tmpMinuteDifference = -15 and tmpHourDifference = -1 and
tmpDayDifference = 0 and tmpYearDifference = 0

why is the tmpHourDifference -1?

Likewise if AP_Start_Date = "1/7/2009" and txAlarmTime = "1/6/2010 09:00:00
am" then tmpMinuteDifference = 0 and tmpHourDifference = 0 and
tmpDayDifference = -364 and tmpYearDifference = -1

why is tmpYearDifference -1?

Thanks for any help you can offer!
 
I have the following on a form in AC2003.
txAlarmTime is in general format "mm/dd/yyyy hh:mm:ss ampm"

Dim tmpMinuteDifference, tmpHourDifference, tmpDayDifference,
tmpYearDifference As Variant

tmpMinuteDifference = DateDiff("n", Format(Me.AP_Start_Time,
"hh:mm:ss"), Format(Me.txAlarmTime, "hh:mm:ss"))
MsgBox tmpMinuteDifference & " minutes"

tmpHourDifference = DateDiff("h", Format(Me.AP_Start_Time, "hh:mm:ss"),
Format(Me.txAlarmTime, "hh:mm:ss"))
MsgBox tmpHourDifference & " hours"

tmpDayDifference = DateDiff("d", Format(Me.AP_Start_Date, "mm/dd/yyyy"),
Format(Me.txAlarmTime, "mm/dd/yyyy"))
MsgBox tmpDayDifference & " days"

tmpYearDifference = DateDiff("yyyy", Format(Me.AP_Start_Date,
"mm/dd/yyyy"), Format(Me.txAlarmTime, "mm/dd/yyyy"))
MsgBox tmpYearDifference & " years"

if AP_State_Date = 1/6/2010 and txAlarmTime = "1/6/2010 08:45:00"
then tmpMinuteDifference = -15 and tmpHourDifference = -1 and
tmpDayDifference = 0 and tmpYearDifference = 0

why is the tmpHourDifference -1?

Likewise if AP_Start_Date = "1/7/2009" and txAlarmTime = "1/6/2010 09:00:00
am" then tmpMinuteDifference = 0 and tmpHourDifference = 0 and
tmpDayDifference = -364 and tmpYearDifference = -1

why is tmpYearDifference -1?

Thanks for any help you can offer!
Click to expand...

Well... I'm not sure why it's not 0, but there are two things you should be
aware of: you're feeding String values (the result of the Format() function
call) to DateDiff, whereas it expects Date/Time values; and secondly, DateDiff
calculates the number of *changes* of its granularity argument. That is,
DateDiff("yyyy", #12/31/2009 11:59:59PM#, #1/1/2010 00:00:00#) will return one
year, even though only one second has elapsed.

You might want to look at http://www.accessmvp.com/djsteele/Diff2Dates.html -
it's a more flexible replacement for DateDiff that may do exactly what you
want.
 
John... I took a look at Diff2Dates and am using that. It's infinately
easier than all of the code I wrote. Thanks for pointing me in the right
direction.
 
John... I took a look at Diff2Dates and am using that. It's infinately
easier than all of the code I wrote. Thanks for pointing me in the right
direction.
Click to expand...

No point in reinventing the wheel! Glad to have been of help.
 
Back
Top