*&*&*&* Date math question *&*&*&*
Author Message
*&*&*&* Date math question *&*&*&*

How do I subtract a date stored as an 8 char string "CCCCMMDD" (dBase
Date field) from the current system date to retrieve the difference in
days between these 2 values.

Sun, 14 Feb 1999 03:00:00 GMT
*&*&*&* Date math question *&*&*&*

Quote:

>How do I subtract a date stored as an 8 char string "CCCCMMDD" (dBase
>Date field) from the current system date to retrieve the difference in
>days between these 2 values.

Look in the help-file or your documentation: Function DateDiff

Rainer

------------------------------------------------------

Mon, 15 Feb 1999 03:00:00 GMT
*&*&*&* Date math question *&*&*&*

Quote:
> >How do I subtract a date stored as an 8 char string "CCCCMMDD" (dBase
> >Date field) from the current system date to retrieve the difference in
> >days between these 2 values.
> Look in the help-file or your documentation: Function DateDiff

Well let's not be too terse!  Afterall, we are all beginners once and
the manual and help files are only useful once you are beyond a certain
threshold of experience.

DateDiff is an only moderately useful function for calculating the
"elapsed time" between two dates.  That is, it can only calculate Dates,
not hours and minutes -- documentation to the contrary notwithstanding.

The MS KB offers a workaround user-defined function, which I quote in
relevant part:

[quote]
GetElapsedTime() Sample Function
--------------------------------

To create the GetElapsedTime() function, follow these steps:

1. Create a new table with the following structure and save it as
TimeLog.

Table: TimeLog
-----------------------
Field Name: StartTime
Data Type: Date/Time
Format: General Date
Field Name: EndTime
Data Type: Date/Time
Format: General Date

2. View the TimeLog table in Datasheet view, enter the following three
records, and then close the table:

StartTime                 EndTime
----------------------------------------------
5/10/95 4:57:00  P.M.    5/15/95 2:38:00 A.M.
5/11/95 10:17:31 A.M.    5/24/95 6:05:00 P.M.
5/18/95 9:16:43  A.M.    5/19/95 5:03:00 P.M.

3. Create a module and type the following line in the Declarations
section:

Option Explicit

4. Enter the following function.

NOTE: In the following sample code, an underscore (_) is used as a
line-
continuation character. Remove the underscore when re-creating this
code
in Access Basic.

Function GetElapsedTime (interval)
Dim totalhours As Long, totalminutes As Long, totalseconds As
Long
Dim days As Long, hours As Long, Minutes As Long, Seconds As
Long

days = Int(CSng(interval))
totalhours = Int(CSng(interval * 24))
totalminutes = Int(CSng(interval * 1440))
totalseconds = Int(CSng(interval * 86400))
hours = totalhours Mod 24
Minutes = totalminutes Mod 60
Seconds = totalseconds Mod 60

GetElapsedTime = days & " Days " & hours & " Hours " & Minutes & _
" Minutes " & Seconds & " Seconds "

End Function

5. To test this function, create a new report based on the TimeLog table
using the AutoReport Wizard.

6. View the report in Design view.

7. Add an unbound text box to the TimeLog table's detail section and set
its properties as follows:

Textbox:
Name: ElapsedTime
ControlSource: =GetElapsedTime([EndTime]-[StartTime])
Width: 3 inches

8. Preview the report. Note that each record displays the total elapsed
time in days, hours, minutes, and seconds.

GetTimeCardTotal() Sample Function
----------------------------------

To create the GetTimeCardTotal() function, follow these steps:

1. Create a new table with the following structure and save it as
TimeCard.

Table: TimeCard
-----------------------
Field Name: Daily Hours
Data Type: Date/Time
Format: Short Time

2. View the TimeCard table in Datasheet view, enter the following four
records, and then close the table:

8:15
7:37
8:12
8:03

3. Create a module and type the following line in the Declarations
section

Option Explicit

4. Type the following function:

Function GetTimeCardTotal ()
Dim db As Database, rs As Recordset
Dim totalhours As Long, totalminutes As Long
Dim days As Long, hours As Long, minutes As Long
Dim interval As Variant, j As Integer

Set db = dbengine.workspaces(0).databases(0)
Set rs = db.OpenRecordset("timecard")
interval = #12:00:00 AM#
For j = 0 To rs.recordcount - 1
interval = interval + rs![Daily hours]
rs.MoveNext
Next j
totalhours = Int(CSng(interval * 24))
totalminutes = Int(CSng(interval * 1440))
hours = totalhours Mod 24
minutes = totalminutes Mod 60

GetTimeCardTotal = totalhours & " hours and " & minutes & "
minutes"

End Function

5. To test this function, type the following line in the DeBug Window
(or
Immediate window in version 1.x and 2.0), and then press ENTER:

? GetTimeCardTotal ()

Note that the DeBug Window displays 32 hours and 7 minutes.

REFERENCES
==========

following article in the Microsoft Knowledge Base:

ARTICLE-ID: Q130514
TITLE     : Storing, Calculating, and Comparing Date/Time Data

item number Q130514 from the FastTips Main Menu.

[end quote]

Mon, 15 Feb 1999 03:00:00 GMT
*&*&*&* Date math question *&*&*&*

Hi Fred,

I thought something like this:

Suppose your dBase date is in a variable called dBDate, which is a string.

Dim Date1 as Long, Date2 as Long, Difference as Integer

Date1 = Int(CVDate(Right\$(dBDate,2) + "-" + Mid\$(dBDate,5,2) + "-" +
Left\$(dBDate,4)
Date2 = Int(Now)
Difference = Date2 - Date1

It works with me

Quote:
> How do I subtract a date stored as an 8 char string "CCCCMMDD" (dBase
> Date field) from the current system date to retrieve the difference in
> days between these 2 values.

Sat, 20 Feb 1999 03:00:00 GMT
*&*&*&* Date math question *&*&*&*

It was on 3 Sep 1996 15:37:22 GMT, that "Th.M.J. Hollenberg"

thoughtful reflections:

Quote:
>!<Hi Fred,
>!<I thought something like this:
>!<Suppose your dBase date is in a variable called dBDate, which is a string.
>!<Dim Date1 as Long, Date2 as Long, Difference as Integer
>!<Date1 = Int(CVDate(Right\$(dBDate,2) + "-" + Mid\$(dBDate,5,2) + "-" +
>!<Left\$(dBDate,4)
>!<Date2 = Int(Now)
>!<Difference = Date2 - Date1
>!<It works with me

>!<> How do I subtract a date stored as an 8 char string "CCCCMMDD" (dBase
>!<> Date field) from the current system date to retrieve the difference in
>!<> days between these 2 values.
>!<>

>!<>
>!<>

I am sorry to intrude, but is there anything wrong with the DateDiff function
against CDate("CCCCMMDD")?

Mon, 22 Feb 1999 03:00:00 GMT
*&*&*&* Date math question *&*&*&*

You can use DateDiff & DateSerial for counting with dates !!!
If you use strings, you get problems with different date-formats

Marc Gruben

Quote:
> It was on 3 Sep 1996 15:37:22 GMT, that "Th.M.J. Hollenberg"

> thoughtful reflections:

> >!<Hi Fred,

> >!<I thought something like this:

> >!<Suppose your dBase date is in a variable called dBDate, which is a
string.

> >!<Dim Date1 as Long, Date2 as Long, Difference as Integer

> >!<Date1 = Int(CVDate(Right\$(dBDate,2) + "-" + Mid\$(dBDate,5,2) + "-" +
> >!<Left\$(dBDate,4)
> >!<Date2 = Int(Now)
> >!<Difference = Date2 - Date1

> >!<It works with me

> >!<> How do I subtract a date stored as an 8 char string "CCCCMMDD"
(dBase
> >!<> Date field) from the current system date to retrieve the difference
in
> >!<> days between these 2 values.
> >!<>

> >!<>
> >!<>

> I am sorry to intrude, but is there anything wrong with the DateDiff
function
> against CDate("CCCCMMDD")?

Tue, 23 Feb 1999 03:00:00 GMT

 Page 1 of 1 [ 6 post ]

Relevant Pages