UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Date\Time Functions, Any Version    
 
   
CyberCow
post Mar 28 2012, 11:54 PM
Post#1


UdderAccess Admin + UA Ruler
Posts: 19,555
Joined: 27-April 02
From: Upper MI


A few years ago I found some class code and made some modifications to it. Tried to find where it was posted here before, but I was unsuccessful in locating the original post and thought it would most likely be here in the Code Archive, but I guess it was never posted in its entirety here. So, based on Allen Browne's "Returning more than one value from a function", our own Bob "raskew" generated some very handy Date & Time functions; with modifications by UA's datAdrenaline and myself.
On the code you'll see a couple of different versions of a "GetAge" function and a few other goodies for handling, determining and manipulating date\time values. The three constants at the top are not used specifically in the code, but are there to provide very accurate constants for 'seconds', 'minutes' and 'hours' that can be applied against a whole day value, which is "1".
May you find this useful.
Modified on 11-8-2012 by Cybercow - added myGetHours function
Modified on 11-14-2012 by Cybercow - added DateAsWords function (Change Date to Words)
Modified on 11-24-2012 by Cybercow - added myWeekRange (Going from Week Number to Date Range)
Modified on 11-28-2012 by Cybercow - corrected date constants at the top (thanks moishy) and incorporatd dflak's PriorDOW function without the Excel declaration for use in Access)

Attached File(s)
Attached File  1984749.zip ( 7.12K )Number of downloads: 172
 
Go to the top of the page
 
CyberCow
post Nov 8 2012, 10:40 AM
Post#2


UdderAccess Admin + UA Ruler
Posts: 19,555
Joined: 27-April 02
From: Upper MI


Update Note: original post code updated . . .
dded "myGetHours" function to render the difference between two date/time values.
The two date/time values can be provided as strings or dates.
The output can be modified to render d:h:m or x days, y hours, z minutes or other by tweaking the code at line 330 & 340 accordingly.
Go to the top of the page
 
dflak
post Nov 9 2012, 11:36 AM
Post#3


Utter Access VIP
Posts: 6,017
Joined: 22-June 04
From: North Carolina


I would like to submit the following for inclusion as a Datetime Function. I developed it for Excel, but it should also work in Access. Give it a date and a day of the week. It will return the last prior date on which the day of the week occured. For example if TODAY() is Monday then PriorDOW(TODAY(),"Fri") is last Friday. PriorDOW(Today(),"Mon") is the previous Monday unless an optional TRUE flag is used. The TRUE flag allows the function to return the currrent date. Most of the reports I run are due as of the previous whatever-day-of-week.
CODE
Option Explicit
unction PriorDOW(MyDate As Date, DOW As String, Optional UseToday As Boolean) As Date
Dim WD As Long
Dim DOWNum As Long
Dim NewDate As Date
Dim Subtractor As Long
Application.Volatile
WD = WeekDay(MyDate, vbMonday) - 1
If UseToday = True Then
    Subtractor = 0
Else
    Subtractor = 7
End If
Select Case DOW
    Case "Mon"
        DOWNum = 0
    Case "Tue"
        DOWNum = 1
    Case "Wed"
        DOWNum = 2
    Case "Thu"
        DOWNum = 3
    Case "Fri"
        DOWNum = 4
    Case "Sat"
        DOWNum = 5
    Case "Sun"
        DOWNum = 6
    Case Else
        DOWNum = 0
End Select
If WD = DOWNum Then
    NewDate = MyDate - Subtractor
Else
    NewDate = MyDate - (WD + 7 - DOWNum)
End If
If MyDate - NewDate > 7 Then
    NewDate = NewDate + 7
End If
PriorDOW = NewDate
End Function
Go to the top of the page
 
CharlesWilliams
post May 14 2015, 11:04 AM
Post#4



Posts: 124
Joined: 3-September 02
From: Philadelphia, PA


Here is my couple of tweaks to the DateAsWords code as follows:

Dim tm As String

Select Case Right(Day(dt), 1)
Case 1
If Day(dt) > 9 And Day(dt) < 14 Then
DateAsWords = Day(dt) & "th"
Else
DateAsWords = Day(dt) & "st"
End If
Case 2
If Day(dt) > 9 And Day(dt) < 14 Then
DateAsWords = Day(dt) & "th"
Else
DateAsWords = Day(dt) & "st"
End If
Case 3
If Day(dt) > 9 And Day(dt) < 14 Then
DateAsWords = Day(dt) & "th"
Else
DateAsWords = Day(dt) & "nd"
End If
Case 4
If Day(dt) > 9 And Day(dt) < 14 Then
DateAsWords = Day(dt) & "th"
Else
DateAsWords = Day(dt) & "rd"
End If
Case Else
DateAsWords = Day(dt) & "th"
End Select

Select Case f
Case 0
' Change nothing
Case 1
DateAsWords = Format(dt, "mmmm") & " " & DateAsWords & ", " & Year(dt)
Case 2
DateAsWords = Format(dt, "dddd") & " " & Format(dt, "mmmm") & " " & DateAsWords & ", " & Year(dt)
Case 3
DateAsWords = DateAsWords & " of " & Format(dt, "mmmm") & ", " & Year(dt)
Case 4
DateAsWords = DateAsWords & " of " & Format(dt, "mmmm")
End Select

In my tweaks I made case 2 case 3 and case 3 case 4. I made case 2 and added the day of the week in the beginning. (Re: Thursday May 14th, 2015) for those who would want to show the day of the week before the date.

This is just my personal tweak.

- Charles
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    18th December 2017 - 03:28 PM