Converting Short Time into Decimal

[Scott]

I have a query that has a BeginTime and EndTime field. Both are medium time.
I then have a "Duration" expression like Figure 1 that subtracts the
BeginTime and EndTime and returns in short time.

I'm trying to write an expression for a 4th column that converts the
"Duration" into decimal format.

Anyone have a suggestion?

FIGURE 1
Duration: Format([BeginDateTime]-1-[EndDateTime],"Short Time")

Example:

BeginTime EndTime Duration Decimal
8:30 AM 8:47 AM 00:17 ???
3:23 PM 3:30 PM 00:07

 

[Ronald Dodge]

Not sure if you want the decimal to be what decimal of the day it is or what
decimal of the hour it is, so I will give both.

Assumption:

Duration is in String format.

Dim Min as Long, Hr as Long, dblDuration as Double, P as Long, txtDuration
as String
txtDuration = LabelDuration.Caption
P = InStr(1,txtDuration,":",0)
Hr = CLng(VBA.Strings.Left(txtDuration,P-1))
Min = CLng(VBA.Strings.Mid(txtDuration,P+1))
'Hour Duration Calculation
dblDuration = Hr + Min / 60
'Day Duration Calculation
dblDuration = dblDuration / 24

 

[Graham Mandeno]

Hi Scott

First, your Duration calculation is incorrect, and only works because of a
quirk in the way Access stores dates and times. To find a duration, you
subtract the start time from the end time, not the other way around:

Duration: Format([EndDateTime]-[StartDateTime],"Short Time")

Note also that this will only work for durations less than 24 hours.

For the decimal, use the DateDiff function to find the difference between
the times in minutes, then divide by 60 to give the number of hours (or
divide by 1440 to give the number of days:

Hours: DateDiff( "n", [StartDateTime], [EndDateTime] ) / 60

 

[Kelvin]

Since Access stored dates and times as a number you could just convert the
duration you calculated into the decimal equivalent using CDbl() or CDec().
This will give you minutes equal to your duration divided by 1440.

Kelvin

 

[Ronald Dodge]

I must beg to differ about the use of the DateDiff function.

If the start time is 12:54 and the end time is 14:12, and you use the
DateDiff to return the number of hours, it will return 2, not 1 as expected.
That is why I don't use the DateDiff function for these types of situations.

--
Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

Hi Scott

First, your Duration calculation is incorrect, and only works because of a
quirk in the way Access stores dates and times. To find a duration, you
subtract the start time from the end time, not the other way around:

Duration: Format([EndDateTime]-[StartDateTime],"Short Time")

Note also that this will only work for durations less than 24 hours.

For the decimal, use the DateDiff function to find the difference between
the times in minutes, then divide by 60 to give the number of hours (or
divide by 1440 to give the number of days:

Hours: DateDiff( "n", [StartDateTime], [EndDateTime] ) / 60

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a query that has a BeginTime and EndTime field. Both are medium time.
I then have a "Duration" expression like Figure 1 that subtracts the
BeginTime and EndTime and returns in short time.

I'm trying to write an expression for a 4th column that converts the
"Duration" into decimal format.

Anyone have a suggestion?

FIGURE 1
Duration: Format([BeginDateTime]-1-[EndDateTime],"Short Time")

Example:

BeginTime EndTime Duration Decimal
8:30 AM 8:47 AM 00:17 ???
3:23 PM 3:30 PM 00:07

 

[Ronald Dodge]

One thing that I didn't mention, the way you have the calculation setup,
it's not accurate, you must subtract the starting time from the ending time.
Example:

Dim dblTimeElaspe as Double, strTimeElaspe as String
dblTimeElaspe = ([EndDateTime]-[StartDateTime])*24
strTimeElaspe = CStr(Int(dblTimeElaspe)) & ":" &
CStr(Int((dblTimeElaspe-Int(dblTimeElaspe))*60))

dblTimeElaspe shows how many hours in decimal format has elasped and this
works for more than 24 hours too.

strTimeElaspe shows how many hours and minutes has elasped in string format,
which also works for more than 24 hours.

--
Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

Not sure if you want the decimal to be what decimal of the day it is or what
decimal of the hour it is, so I will give both.

Assumption:

Duration is in String format.

Dim Min as Long, Hr as Long, dblDuration as Double, P as Long, txtDuration
as String
txtDuration = LabelDuration.Caption
P = InStr(1,txtDuration,":",0)
Hr = CLng(VBA.Strings.Left(txtDuration,P-1))
Min = CLng(VBA.Strings.Mid(txtDuration,P+1))
'Hour Duration Calculation
dblDuration = Hr + Min / 60
'Day Duration Calculation
dblDuration = dblDuration / 24

--
Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

I have a query that has a BeginTime and EndTime field. Both are medium time.
I then have a "Duration" expression like Figure 1 that subtracts the
BeginTime and EndTime and returns in short time.

I'm trying to write an expression for a 4th column that converts the
"Duration" into decimal format.

Anyone have a suggestion?

FIGURE 1
Duration: Format([BeginDateTime]-1-[EndDateTime],"Short Time")

Example:

BeginTime EndTime Duration Decimal
8:30 AM 8:47 AM 00:17 ???
3:23 PM 3:30 PM 00:07

 

[Rick Brandt]

I must beg to differ about the use of the DateDiff function.

If the start time is 12:54 and the end time is 14:12, and you use the
DateDiff to return the number of hours, it will return 2, not 1 as expected.
That is why I don't use the DateDiff function for these types of

situations.

Correct. DateDiff counts "boundaries crossed" so...

DateDiff("h", #12/31/2002 23:59:59#, #1/1/2003 00:00:01#)

....returns 1 even though they are only one second apart. The trick is to
always use the increment one place smaller than the one you want rounded
resolution results on. So, if you use DateDiff() using minutes and then
divide by 60 you can get the results you want.

 

[Ronald Dodge]

Yes, that will help reduce the chances of an incorrect result, but it still
has about a 1 in 120 chance of producing an incorrect result in this case.
Since the Date/Time stuff is already in Double Data Type format, I just
calculate the figures directly, which eliminates the chances of ever
producing an incorrect result unless it resulted from some other situation
such as a user forgets to click on Run after setting up the machine center,
thus causing the Setup Time to report longer than it really was and Run time
to report shorter than what it really was. But even then, the direct
calculations wouldn't be the blame for the incorrect results, but rather on
the user's forgetfulness, of which we are all humans, so those kinds of
things are bound to happen from time to time.

 

[Graham Mandeno]

Hi Ronald

I must beg to differ about the use of the DateDiff function.

You're welcome to differ - no need to beg

If the start time is 12:54 and the end time is 14:12, and you use the
DateDiff to return the number of hours, it will return 2, not 1 as expected.
That is why I don't use the DateDiff function for these types of

situations.

....which is why my example used DateDiff to calculate the number of
*minutes* (not hours) between the two times, and divided by 60. Of course,
you could also use the difference in seconds and divide by 3600, but I
inferred from Scott's original post that the recording granularity was
minutes, so there's no need.

While I agree that straight floating-point arithmetic of date/time variables
*usually* works, there are some problems involved:

1) The way date/time values are stored internally is mot documented and so
could conceivably change.

2) Different languages and applications actually use different data
structures (try subtracting an Access date from an Excel date)

3) There are certain conditions where the arithmetic will simply not work.
Try this in the debug window:

?DateDiff("n",#29-dec-1899 22:30#,#30-dec-1899 01:30#)/60
3

?(cdbl(#30-dec-1899 01:30#)-cdbl(#29-dec-1899 22:30#))*24
48

48 hours between 10:30 one night and 1:30 the following morning? I don't
think so!

 

[Ronald Dodge]

See inline comments:

--
Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

Hi Ronald


You're welcome to differ - no need to beg

situations.

...which is why my example used DateDiff to calculate the number of
*minutes* (not hours) between the two times, and divided by 60. Of course,
you could also use the difference in seconds and divide by 3600, but I
inferred from Scott's original post that the recording granularity was
minutes, so there's no need.

While I agree that straight floating-point arithmetic of date/time variables
*usually* works, there are some problems involved:

1) The way date/time values are stored internally is mot documented and so
could conceivably change.

Actually, *IT IS* documented. If you go to Access Help (can get to by going
onto a form in design view, click on Help, Microsoft Access Help

Then drill down the topics of:

Microsoft Access Help>Programming in Visual Basic>Visual Basic Language
Reference>Data Types

Then compare Date Data Type and Double Data Type. Set aside from the date
range that accepted, the very first statement of the Date Data Type matches
to the very first statement of Double Data Type:

Date Data Type
Date variables are stored as IEEE 64-bit (8-byte) floating-point numbers...

Double Data Type
Double (double-precision floating-point) variables are stored as IEEE 64-bit
(8-byte) floating-point numbers...

2) Different languages and applications actually use different data
structures (try subtracting an Access date from an Excel date)

Actually, Excel and all other MS Office applications use the same common
data types which is found under the drill down topics of:

Visual Basic Language Reference>Data Types

Now, if you talking about other applications set aside from MS Office
Applications, yes, there could be some differences, like in JDE (JD Edwards
DB System), the date and time storage is stored very much different, but
again, there are such functions available to help convert these different
date/time formats when working accross the different applications, which is
the responsibility of the developer to know what are the different formats
used and to use these different functions as needed. Trust me, I have
worked with various date/time formats over the years. I have at times even
created my own conversions like converting UTC (Universal Time Code)
date/time format to VBA's Date/Time format. Along with that, I have also
had to setup the code to not only take into account the different formats,
but also take into account the time zone differences, and when to take
daylight savings into account versus when not to as UTC typically is stored
in Greenwich Mean Time and is generally in standard time year round, of
which there is a programming reason why it's normally done that way.

3) There are certain conditions where the arithmetic will simply not work.
Try this in the debug window:

?DateDiff("n",#29-dec-1899 22:30#,#30-dec-1899 01:30#)/60
3

?(cdbl(#30-dec-1899 01:30#)-cdbl(#29-dec-1899 22:30#))*24
48

48 hours between 10:30 one night and 1:30 the following morning? I don't
think so!

You need to use the CDate function to convert these date literals to it's
date/time decimal format. One thing though, it's not documented (from what
I could see anyhow) that you can necessarily combine both the date and time
in the same literal date expression, so you may have to do the date literal
and time literal separately.

--
HTH

Graham Mandeno [Access MVP]
Auckland, New Zealand

 

[Graham Mandeno]

Hi Ronald

Date Data Type
Date variables are stored as IEEE 64-bit (8-byte) floating-point

numbers...

Certainly, it is documented that they are stored in double-floating numbers,
but what is *not* documented (to my knowledge) is the relationship between
that number and actual dates, nor the slightly unexpected behaviour which
occurs when the number goes negative.

It is a popular misconception (I have publicly stated it myself) that the
numeric value represents the number of whole days and fractions of a day
after *or before* "Time Zero" (30-Dec-1899 00:00). This is not the case,
which explains why a subtraction over that boundary time does not work.

In actual fact, the integer part of that number is the number of midnights
before or since "Time Zero" and the fraction part is the fraction of a day
*after* that time. Try the following:
?format(cdate(#30-Dec-1899 00:00# - #3:00#), "dd-mmm-yyyy hh:nn")

Perhaps the expected result is 29-Dec-1899 21:00 (three hours before
midnight) but in actual fact you get
30-Dec-1899 03:00.

This is because the subtraction gives a numeric result of -0.125. This is
interpreted as:
Day = -0 = 0 = 30-Dec-1899
Time = 0.125 (not -0.125) = 1/8 of a day = 3 hours = 03:00

The alternative:
?format(DateAdd("h", -3, #30-Dec-1899#), "dd-mmm-yyyy hh:nn")
gives the correct result.

Actually, Excel and all other MS Office applications use the same common
data types which is found under the drill down topics of:

VBA in Excel does, but not Excel itself. From the Excel 2003 help:

------- Start quote ------
Excel supports two date systems: the 1900 and 1904 date systems. The default
date system for Microsoft Excel for Windows is 1900. The default date system
for Microsoft Excel for the Macintosh is 1904. You can change the date
system. On the Tools menu, click Options, click the Calculation tab, and
then select or clear the 1904 date system check box.
The date system is changed automatically when you open a document from
another platform. For example, if you are working in Excel for Windows and
you open a document created in Excel for the Macintosh, the 1904 date system
check box is selected automatically.

The following table shows the first date and the last date for each date
system and the serial value associated with each date.

Date system First date Last date
1900 January 1, 1900 December 31, 9999
(serial value 1) (serial value 2958465)
1904 January 2, 1904 December 31, 9999
(serial value 1) (serial value 2957003)
-------- End quote ---------------

This means that the date *number* for 1-Jan-1900 in Excel for Windows is 1,
while in Access (and VBA) it is 2. This discrepancy disappears after
1-Mar-1900 because Excel erroneously treats 1900 as a leap year (try
=DateValue("29-Feb-1900") in an Excel worksheet.)

My whole point is that if you use the date/time manipulation functions that
are provided, you will always get the expected result. Results from
fiddling "under the hood" must be treated with caution. That said, it would
be nice if DateAdd and DateDiff recognised the existence of fractions

 

[Ronald Dodge]

Well, I can certainly understand your points with regards to the date/time
issues at hand for when going from positive to negative or vice versa and
along with the fact of how the negative number works. There's 2 things
that's going on here.

1) As you stated, the decimal portion does represent how much time has past
by *SINCE* midnight, of which that is documented, so with that in mind, the
following code should work regardless with respect to the time portion:

VarTime = Abs(MyDate) - Int(Abs(MyDate))

2) As far as the date portion is concerned, it's basically using the
truncating method, thus why you get the unexpected result, and since
Truncate is not available in VBA without necessarily refering to the
Worksheet function in Excel, you need to use the following code:

VarDate = Int(MyDate - (Abs(MyDate) -
Int(Abs(MyDate)))*(MyDate/Abs(MyDate)))

Now for the Date issues in Excel that you point out.

First, the erroroneous of having a 2/29/1900, yes, that's an error that is
most definitely a programming error, and I would basically hold the vendor
accountable for that error, but since we can't control how they do things,
all we can do is work around it. Dispite this Excel spreadsheet date error,
the CDate in VBA still works in Excel VBA as expected, so you will need to
test for the value in Excel before you can continue on with your date/time
calcs, is you are gonna rely on the values in the Excel spreadsheet.

Now the Excel Date using the 1904 system.

This was done to be compatible with Mac Systems of which I'm guessing 2
things based on the fact that January 1, 1904 has a value of 0.

First, they probably didn't want to have to deal with the leap year issue
starting in 1900 right off the back, though they could have dealt with it.

Second, I'm also guessing that they used the same thought process as who
ever set up the UTC code, but only instead of how many seconds since January
1, 1970 at midnight(the start of the day, not the end of the day) in GMT
Standard Time, it's how many days (including portions of a day) since
Midnight on January 1904. Now the one other issue with regards to the date
system on the spreadsheet side, it does not accept a number that's lower
then the first number of the date system, which means, the spreadsheet side
of Excel will not accept a date before the year 1900 and in the 1904 date
system case, not a date before the year 1904, just like the UTC doesn't
accept a time before January 1st, 1970 @ 12:00:00 GMT Stardard Time (cause
every single bit in a 32 bit program is used for the date/time from January
1, 1970 @ 12:00:00 AM GMT Standard Time to January 19, 2038 @ 3:14:07 AM GMT
Standard Time)

Given the difference, the 1904 Date system is just systematically shifted
1462 days (1 for the value starting with 0 for the 1904 date system instead
of 1 like the 1900 date system, 1460 for the actual number of days different
between 1/1/1900 and 1/1/1904, and 1 for the erroneous inclusive date of
2/29/1900). This shift is easily programmatically done.

As I have mentioned before, it's ultimately the developers responsibility to
figure out these different things, and to create work arounds to address the
different potential issues.