 |
|||||||||
| Miscellaneous | |||||||||
Date & Time
| # | TOPIC | PROG | CODE | NOTES | |
|---|---|---|---|---|---|
1 |
Day Of Week |
ACCESS |
In a created field, type: DOW: Format([date field],"dddd") |
A [date field] value of 6/13/2012 will return: Wednesday |
|
2 |
Day Of Week |
EXCEL |
In the results cell, type: =TEXT(A2,"dddd") |
Cell A2 contains the data in this example. |
|
3 |
Current Date |
ACCESS |
Date() |
Returns value similar to: 6/13/2012 |
|
4 |
Current Date |
EXCEL |
In the results cell, type: =TODAY() |
Returns value similar to: 6/13/2012 |
|
5 |
Current Date And Time |
ACCESS |
Now() |
Returns value similar to: 6/13/2012 3:40:35 PM |
|
6 |
Current Date And Time |
||||
7 |
Filtering Date from Now Function |
ACCESS |
Format([DateField],"yyyy-mm-dd") |
DateField = Now() |
|
8 |
Filtering Time from Now Function |
ACCESS |
Format([DateField],"HH:MM:SS") |
DateField = Now() |
|
9 |
First Day Of Month |
ACCESS |
In a created field, type:
FDOM:DateSerial(Year([date field]),Month([date field]),1) |
||
10 |
Last Day Of Month |
ACCESS |
In a created field, type: LDOM:DateSerial(Year([date field]),Month([date field]),+1,0) |
||
11 |
First Day Of Year |
ACCESS |
In a created field, type: FDOY: DateSerial(Year([date field]),1,1) |
||
12 |
Last Day Of Year |
ACCESS |
In a created field, type: LDOY: DateSerial(Year([date field]),+13,0) |
||
13 |
First Day Of Current Month | ACCESS |
|||
14 |
Last Day Of Current Month | ACCESS |
In a created field, type: LDCM: DateSerial(Year(Date()), Month(Date()) + 1,0) |
||
15 |
First Day Of Next Month | ACCESS |
|||
16 |
Last Day Of Next Month | ACCESS |
In a created field, type: LDNM: DateSerial(Year(Date()), Month(Date()) + 2,0) |
||
17 |
First Day Of Previous Month | ACCESS |
In a created field, type: FDPM: DateSerial(Year(Date()), Month(Date()) -1,1) |
||
18 |
Last Day Of Previous Month | ACCESS |
In a created field, type: LDPM: DateSerial(Year(Date()), Month(Date()),0) |
||
19 |
Pull Month From Date |
ACCESS |
In a created field, type: MonthName: Format([date field],"mmmm") |
||
20 |
Pull Month-Number From Date |
ACCESS |
In a created field, type: MonthNumber: Format([date field],"m") |
This will return an integer between 1 and 12. |
|
21 |
Pull Year From Date
|
ACCESS |
In a created field, type: Year: Format([date field],"yyyy") |
This will return the four-digit year. |
|
22 |
Pull Quarter From Date |
ACCESS |
In a created field, type: Quarter: Format([date field],"q") |
This will return either 1,2,3 or 4. |
|
23 |
Pull Month & Year From Date (Format 1) |
ACCESS |
In a created field, type: ShortDate: Format([date field],"mmmm-yy") |
This will return a period similar to: Mar-2002 |
|
24 |
Pull Month & Year From Date (Format 2) |
ACCESS |
In a created field, type: ShortDate: Format([date field],"m-yyy") |
This will return a period similar to: 3/2002 |
|
25 |
Pull Short Date |
ACCESS |
In a created field, type: ShortDate: Format([date field],"Short Date") |
||
26 |
ACCESS |
||||
27 |
Convert String To A Date |
ACCESS |
In a created field, type: tempDate: CDate([date text field]) |
||
28 |
Convert a character to a number. |
ACCESS |
In a created field, type: Numeric Year: Val([txtYear]) |
[txtYear] is text-formatted. |
|
29 |
Weekend Function |
ACCESS |
This actually displays the numeric day of the week, but you can use it as a limit to identify records that occur on a Saturday or Sunday. Weekday: Weekday([date field]) |
Saturday = 7, Sunday = 1, Monday = 2, Tuesday = 3, Wednesday = 4 Thursday = 5, Friday = 6 |