How do I subtract a date w/ time from a previous date with time

  • Thread starter Thread starter Biederboat
  • Start date Start date
B

Biederboat

I'm having trouble with this one, I have some raw data in a table that I
successfully formatted to be (in a query), for sake of discussion, a start
date, a start time, an end date and an end time (all seperate fields). I
want to "combine" the end date and time and subtract the start date and time
and get a value in minutes. It seems like I can use Datediff for just times
or just dates but not a date w/ a time. For example, I want to take 7/22/08
11:22 and subtract 7/19/08 4:22 (all times are military format) and get the
answer in minutes.

Any suggestions?

Thanks,
Biederboat
 
all times are military format
You said what the format was but that does not matter unless you field in
the table is text instead of a DateTime DataType.
If all are DateTime DataType the just do it this way ---
DateDiff("x", ([start date] + [start time]), ([end date] + [end
time]))
"x" is the DateDiff you want - 'n', 'm', 'd', etc.

If the field in the table is text then you have to convert the data from
text to DateTime before you can add the date to the time.
 
I'm not sure that is going to work correctly, Karl. The logic is sound, but
all date/time fields carry both a date component and a time component. That
is why on of the resons for carrying dates and times in separate fields is a
bad idea.

To ensure you get the correct values, I would suggest wrapping the date and
time fields in the DateValue and TimeValue functions.

DateDiff("x", DateValue([start date]) + TimeValue([start time]),
DateValue([end date]) + TimeValue([end time]))

--
Dave Hargis, Microsoft Access MVP


KARL DEWEY said:
You said what the format was but that does not matter unless you field in
the table is text instead of a DateTime DataType.
If all are DateTime DataType the just do it this way ---
DateDiff("x", ([start date] + [start time]), ([end date] + [end
time]))
"x" is the DateDiff you want - 'n', 'm', 'd', etc.

If the field in the table is text then you have to convert the data from
text to DateTime before you can add the date to the time.

--
KARL DEWEY
Build a little - Test a little


Biederboat said:
I'm having trouble with this one, I have some raw data in a table that I
successfully formatted to be (in a query), for sake of discussion, a start
date, a start time, an end date and an end time (all seperate fields). I
want to "combine" the end date and time and subtract the start date and time
and get a value in minutes. It seems like I can use Datediff for just times
or just dates but not a date w/ a time. For example, I want to take 7/22/08
11:22 and subtract 7/19/08 4:22 (all times are military format) and get the
answer in minutes.

Any suggestions?

Thanks,
Biederboat
Click to expand...
Click to expand...
 
Thanks guys, I'm a bit embarrased to say that, apparently, it was working the
way I had it. Here's what I already had in place: Load Delay:
DateDiff("n",[pdate] & " " & [pt],[sdate] & " " & [st]). I just didn't
recognize that the results actually were correct. Somehow stringing
everything together into one field and then subtracting them was giving the
correct answer. What clued me in as I got the same exact results as Karl's
method, which prompted me to analyse the data a little closer!

Thanks again,
Biederboat



Klatuu said:
I'm not sure that is going to work correctly, Karl. The logic is sound, but
all date/time fields carry both a date component and a time component. That
is why on of the resons for carrying dates and times in separate fields is a
bad idea.

To ensure you get the correct values, I would suggest wrapping the date and
time fields in the DateValue and TimeValue functions.

DateDiff("x", DateValue([start date]) + TimeValue([start time]),
DateValue([end date]) + TimeValue([end time]))

--
Dave Hargis, Microsoft Access MVP


KARL DEWEY said:
all times are military format
Click to expand...
You said what the format was but that does not matter unless you field in
the table is text instead of a DateTime DataType.
If all are DateTime DataType the just do it this way ---
DateDiff("x", ([start date] + [start time]), ([end date] + [end
time]))
"x" is the DateDiff you want - 'n', 'm', 'd', etc.

If the field in the table is text then you have to convert the data from
text to DateTime before you can add the date to the time.

--
KARL DEWEY
Build a little - Test a little


Biederboat said:
I'm having trouble with this one, I have some raw data in a table that I
successfully formatted to be (in a query), for sake of discussion, a start
date, a start time, an end date and an end time (all seperate fields). I
want to "combine" the end date and time and subtract the start date and time
and get a value in minutes. It seems like I can use Datediff for just times
or just dates but not a date w/ a time. For example, I want to take 7/22/08
11:22 and subtract 7/19/08 4:22 (all times are military format) and get the
answer in minutes.

Any suggestions?

Thanks,
Biederboat
Click to expand...
Click to expand...
Click to expand...
 
Back
Top