Date and Time Functions

[DawnTreader]

Hello All

i have need of a way to calculate the difference between 2 dates and showing
it in a particular format.

one of the dates is the day a product stopped functioning. the second date
could be either the current moment, or a date when the product started
running again. this part is actually quite easy, its the formatting it the
way i want that i am having trouble figuring out.

if i had the date and time of 2008/05/01 12:00pm as my "down" date and time,
and the date and time of 2008/06/02 5:39 pm as my "restarted" date and time i
would like to show it as:

1 Month, 1 Day, 5 Hours

alternatively i could live with

32 Days, 5 Hours

any ideas?

 

[DawnTreader]

Hello

Thanks... that helped, kinda

do it in seconds...

my datediff is turning out in seconds. i know that is weird, but that is
what the boss wants...

 

[DawnTreader]

i think i should be more clear

i need to be able to lay it out in any format starting with weeks

so imagine for example that i want this result:

2 weeks, 4 days, 3 hours, 23 minutes, 4 seconds.

 

[DawnTreader]

the problem i am having is that the days, hours, minutes are the total
seconds turned into minutes. i am unsure of how to remove the amount of
seconds from the whole after calculating the interval.

so i use 604800 seconds for the weeks, 1209600, the left over then needs to
be divided by the amount of seconds in a day, 86400, and the left over of
that needs to be ... etc.

my brain is just not wrapping around it right....

 

[DawnTreader]

actually i intend to use it as a global function. i have the basic plan in
place. i want to be able to show it on forms, use it in queries and reports.

i am thinking, do i just mod till the cows come home?

datedown = #2008/05/01 12:00 PM#
dateup = #2008/06/02 5:39 PM#
timedown = datediff("s", datedown, dateup)

i have created some constants in my function that store how many seconds are
in a week, day, hour and minute. i was trying to figure out how to use them
and return values that i could connect together in a string to return to the
queries and forms.

all i really need is the formula. been trying a few different things but it
keeps coming out with the total weeks, total days, total hours, total minutes
and total seconds that the timedown is.

i really appreciate all help.

 

[Douglas J. Steele]

See whether http://www.accessmvp.com/DJSteele/Diff2Dates.html helps.

 

[DawnTreader]

Thanks Doug... that will save me a lot of time.

See whether http://www.accessmvp.com/DJSteele/Diff2Dates.html helps.

 

[Douglas J. Steele]

ISO standards say that should be yyyy-mm-dd, that yyyy-dd-mm is not valid,
but I have known some individuals who, for some unknown reason, insisted on
using yyyy-dd-mm.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Added: Where are you and what does that date format (scares me to death)
represent?
Is #2008/05/01 12:00 PM# 05-Jan-1008 or 01-May-2008?

Bob

I'm lost! What exactly are you calculating that requires output 'down to
the
second'! Maybe there's a valid reason so I'll wait to hear back before
commenting on your boss'es (that doesn't look right) wisdom.

Bob

actually i intend to use it as a global function. i have the basic plan
in
place. i want to be able to show it on forms, use it in queries and
reports.

[quoted text clipped - 21 lines]

any ideas?

 

[DawnTreader]

Hello

because the application i am working on is used by people in various
countries i am never quite sure of what my users are going to dump in to my
fields.

i use the format 2008-Jun-01 12:31:12 AM as my set up because the month
becomes nonnegotible, meaning when they type in the date with the month and
day backwards it still shows me the proper month.

i know that there might be problems with 2008-01-06 turning into january 06
when the user intended june 01, but then the text should give them a hint
that something went wrong and they should figure it out.

additionally the users have realised that they can put in the three letters
of the month and access knows exactly what they mean, instead of it
continually getting the month and day mixed up.

ISO standards say that should be yyyy-mm-dd, that yyyy-dd-mm is not valid,
but I have known some individuals who, for some unknown reason, insisted on
using yyyy-dd-mm.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Added: Where are you and what does that date format (scares me to death)
represent?
Is #2008/05/01 12:00 PM# 05-Jan-1008 or 01-May-2008?

Bob

I'm lost! What exactly are you calculating that requires output 'down to
the
second'! Maybe there's a valid reason so I'll wait to hear back before
commenting on your boss'es (that doesn't look right) wisdom.

Bob

actually i intend to use it as a global function. i have the basic plan
in
place. i want to be able to show it on forms, use it in queries and
reports.
[quoted text clipped - 21 lines]

any ideas?

 

[John W. Vinson]

additionally the users have realised that they can put in the three letters
of the month and access knows exactly what they mean, instead of it
continually getting the month and day mixed up.

Well... maybe. If they have a German language setting on their PC, then it's
"Mai" not "May", or "Dez" not "Dec".

That doesn't even touch on non-Western settings (Arabic, Hebrew, Chinese, ...)

 

[Douglas J. Steele]

Well... maybe. If they have a German language setting on their PC, then
it's
"Mai" not "May", or "Dez" not "Dec".

That doesn't even touch on non-Western settings (Arabic, Hebrew, Chinese,
...)

I wouldn't think that would matter, John, since as you know it's strictly a
matter of getting Access to recognize the date correctly so that it can
convert it to the correct number (an eight byte floating point number where
the integer portion represents the date as the number of days relative to 30
Dec, 1899, and the decimal portion represents the time as a fraction of a
day). I would hope that the user would be typing the month in the same
language as their version of Windows so that even if one user uses German
and another user uses French against the same back-end, it should work.

 

[DawnTreader]

Oops.

Throw a 'Dim strChar as String' in there.
Got lost in the copy/paste operation.

Bob

Hi -

Give this a try.  Hopefully it'll be self-explanatory.

**************************************************************************­**************
Public Function DtDiff(sdate As Date, edate As Date, Optional pItems As
String = "wdhns") As String
'*******************************************
're:
http://www.accessmonster.com/Uwe/Forum.aspx/access-modulesdaovba/3004...

'Purpose:   Compute date difference in weeks, days, hours, minutes, seconds
'           seconds as specified by operator.
'Coded by:  raskew
'Inputs:    1) ? dtdiff(#2008-May-01 12:00:43 PM#, #2008-Jun-02 5:39:11 PM#,
"wdhns")
'           2) ? dtdiff(#2008-May-01 12:00:43 PM#, #2008-Jun-02 5:39:11 PM#,
"dhn")

'Output:    1) 4 weeks 4 days 5 hours 38 minutes 28 seconds
'           2) 32 days 5 hours 38 minutes
'*******************************************

Dim timehold, xweeks, xdays, xmins, xhrs, xsecs
Dim strHold As String
Dim strKeep As String
Dim n As Integer

'calculate all date/time intervals
timehold = DateDiff("s", sdate, edate)
xweeks = timehold \ 7 * 86400
xdays = timehold \ 86400 - (xweeks * 7)
xhrs = timehold \ 3600 - (xdays * 24) - (xweeks * 7 * 24)
xmins = timehold \ 60 - (xhrs * 60) - (xdays * 24 * 60) - (xweeks * 7 * 24 *
60)
xsecs = timehold Mod 60

'adjust for intervals selected
strHold = "wdhns"
For n = 1 To 5
   strChar = Mid(strHold, n, 1)
   If InStr(pItems, strChar) = 0 Then
       If strChar = "w" Then
           xdays = xdays + (xweeks * 7)
           xweeks = 0
       ElseIf strChar = "d" Then
           xhrs = xhrs + (xdays * 24)
           xdays = 0
       ElseIf strChar = "h" Then
           xmins = xmins + (xhrs * 60)
           xhrs = 0
       ElseIf strChar = "n" Then
           xsecs = xsecs + (xmins * 60)
           xmins = 0
       ElseIf strChar = "s" Then
           xsecs = 0
       End If
   End If
Next n

'Format Output
For n = 1 To Len(pItems)
  strChar = Mid(pItems, n, 1)
  If strChar = "w" Then
     strKeep = strKeep & xweeks & " week" & IIf(xweeks <> 1, "s", "") & " "
  ElseIf strChar = "d" Then
     strKeep = strKeep & xdays & " day" & IIf(xdays <> 1, "s", "") & " "
  ElseIf strChar = "h" Then
     strKeep = strKeep & xhrs & " hour" & IIf(xhrs <> 1, "s", "") & " "
  ElseIf strChar = "n" Then
     strKeep = strKeep & xmins & " minute" & IIf(xmins <> 1, "s", "") & " "
  ElseIf strChar = "s" Then
     strKeep = strKeep & xsecs & " second" & IIf(xsecs <> 1, "s", "") & " "
  End If
Next n

DtDiff = Trim(strKeep)

End Function

**************************************************************************­**************

additionally the users have realised that they can put in the three
letters

[quoted text clipped - 10 lines]

language as their version of Windows so that even if one user uses German
and another user uses French against the same back-end, it should work.

Hello All

i have worked out this problem using the diff2date function i found
through dougs suggestion.

however i have something i want to share with the community that i
added to the function. it wasnt hard to do, and i bet others have
thought of it as well, but i still feel that it is worth sharing.

i needed a way to show just the largest interval out of the diff2date
function, so i added it to the function. here is the completed code:

Public Function Diff2Dates(Interval As String, Date1 As Date, Date2 As
Date, Optional ShowZero As Boolean = False, Optional LargestInterval
As Boolean = False) As Variant
'Author: ) Copyright 2001 Pacific Database Pty Limited
' Graham R Seach MCP MVP (e-mail address removed)
' Phone: +61 2 9872 9594 Fax: +61 2 9872 9593
' This code is freeware. Enjoy...
' (*) Amendments suggested by Douglas J. Steele MVP
' (*) Largest Interval Function by Alan R Tonn
'
'Description: This function calculates the number of years,
' months, days, hours, minutes and seconds between
' two dates, as elapsed time.
'
'Inputs: Interval: Intervals to be displayed (a string)
' Date1: The lower date (see below)
' Date2: The higher date (see below)
' ShowZero: Boolean to select showing zero elements
'
'Outputs: On error: Null
' On no error: Variant containing the number of years,
' months, days, hours, minutes & seconds between
' the two dates, depending on the display interval
' selected.
' If Date1 is greater than Date2, the result will
' be a negative value.
' The function compensates for the lack of any intervals
' not listed. For example, if Interval lists "m", but
' not "y", the function adds the value of the year
' component to the month component.
' If ShowZero is True, and an output element is zero, it
' is displayed. However, if ShowZero is False or
' omitted, no zero-value elements are displayed.
' For example, with ShowZero = False, Interval = "ym",
' elements = 0 & 1 respectively, the output string
' will be "1 month" - not "0 years 1 month".

On Error GoTo Err_Diff2Dates

Dim booCalcYears As Boolean
Dim booCalcMonths As Boolean
Dim booCalcDays As Boolean
Dim booCalcHours As Boolean
Dim booCalcMinutes As Boolean
Dim booCalcSeconds As Boolean
Dim booSwapped As Boolean
Dim dtTemp As Date
Dim intCounter As Integer
Dim lngDiffYears As Long
Dim lngDiffMonths As Long
Dim lngDiffDays As Long
Dim lngDiffHours As Long
Dim lngDiffMinutes As Long
Dim lngDiffSeconds As Long
Dim varTemp As Variant

Const INTERVALS As String = "dmyhns"

'Check that Interval contains only valid characters
Interval = LCase$(Interval)
For intCounter = 1 To Len(Interval)
If InStr(1, INTERVALS, Mid$(Interval, intCounter, 1)) = 0 Then
Exit Function
End If
Next intCounter

'Check that valid dates have been entered
If Not (IsDate(Date1)) Then Exit Function
If Not (IsDate(Date2)) Then Exit Function

'If necessary, swap the dates, to ensure that
'Date1 is lower than Date2
If Date1 > Date2 Then
dtTemp = Date1
Date1 = Date2
Date2 = dtTemp
booSwapped = True
End If

Diff2Dates = Null
varTemp = Null

'What intervals are supplied
booCalcYears = (InStr(1, Interval, "y") > 0)
booCalcMonths = (InStr(1, Interval, "m") > 0)
booCalcDays = (InStr(1, Interval, "d") > 0)
booCalcHours = (InStr(1, Interval, "h") > 0)
booCalcMinutes = (InStr(1, Interval, "n") > 0)
booCalcSeconds = (InStr(1, Interval, "s") > 0)

'Get the cumulative differences
If booCalcYears Then
lngDiffYears = Abs(DateDiff("yyyy", Date1, Date2)) - IIf(Format
$(Date1, "mmddhhnnss") <= Format$(Date2, "mmddhhnnss"), 0, 1)
Date1 = DateAdd("yyyy", lngDiffYears, Date1)
End If

If booCalcMonths Then
lngDiffMonths = Abs(DateDiff("m", Date1, Date2)) - IIf(Format$
(Date1, "ddhhnnss") <= Format$(Date2, "ddhhnnss"), 0, 1)
Date1 = DateAdd("m", lngDiffMonths, Date1)
End If

If booCalcDays Then
lngDiffDays = Abs(DateDiff("d", Date1, Date2)) - IIf(Format$
(Date1, "hhnnss") <= Format$(Date2, "hhnnss"), 0, 1)
Date1 = DateAdd("d", lngDiffDays, Date1)
End If

If booCalcHours Then
lngDiffHours = Abs(DateDiff("h", Date1, Date2)) - IIf(Format$
(Date1, "nnss") <= Format$(Date2, "nnss"), 0, 1)
Date1 = DateAdd("h", lngDiffHours, Date1)
End If

If booCalcMinutes Then
lngDiffMinutes = Abs(DateDiff("n", Date1, Date2)) - IIf(Format$
(Date1, "ss") <= Format$(Date2, "ss"), 0, 1)
Date1 = DateAdd("n", lngDiffMinutes, Date1)
End If

If booCalcSeconds Then
lngDiffSeconds = Abs(DateDiff("s", Date1, Date2))
Date1 = DateAdd("s", lngDiffSeconds, Date1)
End If

'this section connects all the parts together. this is responsible
for giving the string of time intervals.
If LargestInterval = False Then
If booCalcYears And (lngDiffYears > 0 Or ShowZero) Then
varTemp = lngDiffYears & IIf(lngDiffYears <> 1, " years",
" year")
End If

If booCalcMonths And (lngDiffMonths > 0 Or ShowZero) Then
If booCalcMonths Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") &
lngDiffMonths & IIf(lngDiffMonths <> 1, " months", " month")
End If
End If

If booCalcDays And (lngDiffDays > 0 Or ShowZero) Then
If booCalcDays Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") &
lngDiffDays & IIf(lngDiffDays <> 1, " days", " day")
End If
End If

If booCalcHours And (lngDiffHours > 0 Or ShowZero) Then
If booCalcHours Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") &
lngDiffHours & IIf(lngDiffHours <> 1, " hours", " hour")
End If
End If

If booCalcMinutes And (lngDiffMinutes > 0 Or ShowZero) Then
If booCalcMinutes Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") &
lngDiffMinutes & IIf(lngDiffMinutes <> 1, " minutes", " minute")
End If
End If

If booCalcSeconds And (lngDiffSeconds > 0 Or ShowZero) Then
If booCalcSeconds Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") &
lngDiffSeconds & IIf(lngDiffSeconds <> 1, " seconds", " second")
End If
End If
Else
If lngDiffYears >= 1 Then
varTemp = lngDiffYears & IIf(lngDiffYears <> 1, " years",
" year")
ElseIf lngDiffMonths >= 1 Then
varTemp = lngDiffMonths & IIf(lngDiffMonths <> 1, "
months", " month")
ElseIf lngDiffDays >= 1 Then
varTemp = lngDiffDays & IIf(lngDiffDays <> 1, " days", "
day")
ElseIf lngDiffHours >= 1 Then
varTemp = lngDiffHours & IIf(lngDiffHours <> 1, " hours",
" hour")
ElseIf lngDiffMinutes >= 1 Then
varTemp = lngDiffMinutes & IIf(lngDiffMinutes <> 1, "
minutes", " minute")
Else
varTemp = lngDiffSeconds & IIf(lngDiffSeconds <> 1, "
seconds", " second")
End If
End If

If booSwapped Then
varTemp = "-" & varTemp
End If

Diff2Dates = Trim$(varTemp)

End_Diff2Dates:
Exit Function

Err_Diff2Dates:
Resume End_Diff2Dates

End Function


i am now thinking of a way to allow the user to chose how the text for
the intervals should look.