Sum not recoginzing a negative number

[Guest]

I have a report in which I am attempting to calculate elapsed time. One being
the start of work on a job, the second being the completion of the job. The
start time and the end time are entered through a form which creates a record
for each event. In my report I group the two events by day and sum them using
the sum function. In order to force subtraction (subtracting the starting
time from the ending time) I multiply the beginning time by -1 which results
in a minus number. When the report adds the two it does not recognize the
negative number and sums them together as if they were both positive. I use
the following routine to calculate the negative value: IIf([Start]="Y",
((CDbl([Trak_Date_Time]))*-1), CDbl([Trak_Date_Time]))
Where: Trak_Date_Time is date/time. I use CDbl to convert date/time to a
double precision value and multiply by minus 1 to get a negative number for
the stating time.If I manually subtract the converted sarting time from the
end time I get the correct elapsed time. On my report the the end time
appears as a positive and the start time appears as a -. Summing the two
should result in elapsed time. Sum does not recognize the negative value and
adds both together disregarding the minus sign. I'm at a loss. Anyone have a
suggestion?

 

[Allen Browne]

You cannot use normal floating point math with date/time values.

Open the Immediate window (Ctrl+G), and enter:
? CDate(0.25)
As expected, a quarter of a day is 6am - actually 6am on Dec 30 1899.

So what would you expect from:
? CDate(-0.25)
Would you expect that to be 6pm on the day before?
It may surpris you to learn that it is 6am on Dec 30 1899 also.

The reason is that date/time values are defined like this:
- the integer part represents the date, and
- the fraction part represents the time.

In the example above, the zero represents the date (Dec 30, 1899), and the
0.25 represents the time. Applying the same logic to -0.25, the -0
represents the date, and the 0.25 represents the time. Since 0 is the same
as -1, the date is the same. And since 0.25 is the same in both cases, 0.25
and -0.25 both represent the same date and time.

The upshot is that you should always use the built-in functions such as
DateDiff() when working with dates and times. Normal floating-point math
does not apply.

 

[Guest]

Thanks for the response. I thought that CDbl was supposed to result in a
double precision number, which it appears to do. My particular example has a
date of 12/6/2004 2:23:35 AM for the end time and 12/6/2004 1:30:25 AM for a
start time.
After I convert these times to Double precision I get 38327.099710648 for
the end time and 38327.06278935 for the start time. Multiplying by -1 yields
-38327.06278935. Adding these together yields a difference of .036921298.
This multiplied by 24 =.886111152 which is equal to the hours difference,
which converts to 53 minutes and 10 seconds. If this works so well when I
compute it manually it should work in the report. The only problem I see is
that Sum doesn't recognize my negative value and just goes ahead and adds it
as though it were a positive number. Why?

You cannot use normal floating point math with date/time values.

Open the Immediate window (Ctrl+G), and enter:
? CDate(0.25)
As expected, a quarter of a day is 6am - actually 6am on Dec 30 1899.

So what would you expect from:
? CDate(-0.25)
Would you expect that to be 6pm on the day before?
It may surpris you to learn that it is 6am on Dec 30 1899 also.

The reason is that date/time values are defined like this:
- the integer part represents the date, and
- the fraction part represents the time.

In the example above, the zero represents the date (Dec 30, 1899), and the
0.25 represents the time. Applying the same logic to -0.25, the -0
represents the date, and the 0.25 represents the time. Since 0 is the same
as -1, the date is the same. And since 0.25 is the same in both cases, 0.25
and -0.25 both represent the same date and time.

The upshot is that you should always use the built-in functions such as
DateDiff() when working with dates and times. Normal floating-point math
does not apply.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a report in which I am attempting to calculate elapsed time. One
being
the start of work on a job, the second being the completion of the job.
The
start time and the end time are entered through a form which creates a
record
for each event. In my report I group the two events by day and sum them
using
the sum function. In order to force subtraction (subtracting the starting
time from the ending time) I multiply the beginning time by -1 which
results
in a minus number. When the report adds the two it does not recognize the
negative number and sums them together as if they were both positive. I
use
the following routine to calculate the negative value: IIf([Start]="Y",
((CDbl([Trak_Date_Time]))*-1), CDbl([Trak_Date_Time]))
Where: Trak_Date_Time is date/time. I use CDbl to convert date/time to a
double precision value and multiply by minus 1 to get a negative number
for
the stating time.If I manually subtract the converted sarting time from
the
end time I get the correct elapsed time. On my report the the end time
appears as a positive and the start time appears as a -. Summing the two
should result in elapsed time. Sum does not recognize the negative value
and
adds both together disregarding the minus sign. I'm at a loss. Anyone have
a
suggestion?

 

[Allen Browne]

Using your example in the Immediate Window:
? CDate(38327.06278935), CDate(-38327.06278935)
12/6/2004 1:30:25 AM 01/22/1795 1:30:25 AM

Now subtract 3 hours (0.125 of a day). You expect the result to be
10:30:25pm on the day before in both cases? Try it:
? CDate(38327.06278935 - .125), CDate(-38327.06278935 - .125)

You cannot treat date/times as floating point numbers, particularly with
negative values.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the response. I thought that CDbl was supposed to result in a
double precision number, which it appears to do. My particular example has
a
date of 12/6/2004 2:23:35 AM for the end time and 12/6/2004 1:30:25 AM for
a
start time.
After I convert these times to Double precision I get 38327.099710648 for
the end time and 38327.06278935 for the start time. Multiplying by -1
yields
-38327.06278935. Adding these together yields a difference of .036921298.
This multiplied by 24 =.886111152 which is equal to the hours difference,
which converts to 53 minutes and 10 seconds. If this works so well when I
compute it manually it should work in the report. The only problem I see
is
that Sum doesn't recognize my negative value and just goes ahead and adds
it
as though it were a positive number. Why?

You cannot use normal floating point math with date/time values.

Open the Immediate window (Ctrl+G), and enter:
? CDate(0.25)
As expected, a quarter of a day is 6am - actually 6am on Dec 30 1899.

So what would you expect from:
? CDate(-0.25)
Would you expect that to be 6pm on the day before?
It may surpris you to learn that it is 6am on Dec 30 1899 also.

The reason is that date/time values are defined like this:
- the integer part represents the date, and
- the fraction part represents the time.

In the example above, the zero represents the date (Dec 30, 1899), and
the
0.25 represents the time. Applying the same logic to -0.25, the -0
represents the date, and the 0.25 represents the time. Since 0 is the
same
as -1, the date is the same. And since 0.25 is the same in both cases,
0.25
and -0.25 both represent the same date and time.

The upshot is that you should always use the built-in functions such as
DateDiff() when working with dates and times. Normal floating-point math
does not apply.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a report in which I am attempting to calculate elapsed time. One
being
the start of work on a job, the second being the completion of the job.
The
start time and the end time are entered through a form which creates a
record
for each event. In my report I group the two events by day and sum them
using
the sum function. In order to force subtraction (subtracting the
starting
time from the ending time) I multiply the beginning time by -1 which
results
in a minus number. When the report adds the two it does not recognize
the
negative number and sums them together as if they were both positive. I
use
the following routine to calculate the negative value: IIf([Start]="Y",
((CDbl([Trak_Date_Time]))*-1), CDbl([Trak_Date_Time]))
Where: Trak_Date_Time is date/time. I use CDbl to convert date/time to
a
double precision value and multiply by minus 1 to get a negative number
for
the stating time.If I manually subtract the converted sarting time from
the
end time I get the correct elapsed time. On my report the the end time
appears as a positive and the start time appears as a -. Summing the
two
should result in elapsed time. Sum does not recognize the negative
value
and
adds both together disregarding the minus sign. I'm at a loss. Anyone
have
a
suggestion?

 

[Guest]

Thanks again for the insight. It would seem that there should be a conversion
that would convert CDbl to a number that could be added and subtracted. I
think you see what I wanted to do. All I want is the difference between the
two. This would be a simple approach to solving the elapsed time problem. It
works manually as you can see from my example. The Date/Time is really just a
counter that runs on as time passes. Subtracting one from another is an easy
way to get the answer.

My problem is that my data is being held in two separate records which
complicates things. If the data was being held in a single record the
DateDiff function I think would work. Any ideas on how to do this?

Using your example in the Immediate Window:
? CDate(38327.06278935), CDate(-38327.06278935)
12/6/2004 1:30:25 AM 01/22/1795 1:30:25 AM

Now subtract 3 hours (0.125 of a day). You expect the result to be
10:30:25pm on the day before in both cases? Try it:
? CDate(38327.06278935 - .125), CDate(-38327.06278935 - .125)

You cannot treat date/times as floating point numbers, particularly with
negative values.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the response. I thought that CDbl was supposed to result in a
double precision number, which it appears to do. My particular example has
a
date of 12/6/2004 2:23:35 AM for the end time and 12/6/2004 1:30:25 AM for
a
start time.
After I convert these times to Double precision I get 38327.099710648 for
the end time and 38327.06278935 for the start time. Multiplying by -1
yields
-38327.06278935. Adding these together yields a difference of .036921298.
This multiplied by 24 =.886111152 which is equal to the hours difference,
which converts to 53 minutes and 10 seconds. If this works so well when I
compute it manually it should work in the report. The only problem I see
is
that Sum doesn't recognize my negative value and just goes ahead and adds
it
as though it were a positive number. Why?

You cannot use normal floating point math with date/time values.

Open the Immediate window (Ctrl+G), and enter:
? CDate(0.25)
As expected, a quarter of a day is 6am - actually 6am on Dec 30 1899.

So what would you expect from:
? CDate(-0.25)
Would you expect that to be 6pm on the day before?
It may surpris you to learn that it is 6am on Dec 30 1899 also.

The reason is that date/time values are defined like this:
- the integer part represents the date, and
- the fraction part represents the time.

In the example above, the zero represents the date (Dec 30, 1899), and
the
0.25 represents the time. Applying the same logic to -0.25, the -0
represents the date, and the 0.25 represents the time. Since 0 is the
same
as -1, the date is the same. And since 0.25 is the same in both cases,
0.25
and -0.25 both represent the same date and time.

The upshot is that you should always use the built-in functions such as
DateDiff() when working with dates and times. Normal floating-point math
does not apply.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a report in which I am attempting to calculate elapsed time. One
being
the start of work on a job, the second being the completion of the job.
The
start time and the end time are entered through a form which creates a
record
for each event. In my report I group the two events by day and sum them
using
the sum function. In order to force subtraction (subtracting the
starting
time from the ending time) I multiply the beginning time by -1 which
results
in a minus number. When the report adds the two it does not recognize
the
negative number and sums them together as if they were both positive. I
use
the following routine to calculate the negative value: IIf([Start]="Y",
((CDbl([Trak_Date_Time]))*-1), CDbl([Trak_Date_Time]))
Where: Trak_Date_Time is date/time. I use CDbl to convert date/time to
a
double precision value and multiply by minus 1 to get a negative number
for
the stating time.If I manually subtract the converted sarting time from
the
end time I get the correct elapsed time. On my report the the end time
appears as a positive and the start time appears as a -. Summing the
two
should result in elapsed time. Sum does not recognize the negative
value
and
adds both together disregarding the minus sign. I'm at a loss. Anyone
have
a
suggestion?

 

[Allen Browne]

If you can retrieve the value from the other record, then DateDiff() should
work.

If you are having trouble getting the data from the other record a subquery
might do that. If that's a new idea, see:
http://support.microsoft.com/default.aspx?scid=kb;en-us;209066