Subtracting Short Time on a Second Shift

G

Guest

I am using a query to calculate machine time in a facility. I am subtracting
Load Time from Offload Time in general it works well except for on the night
shift where they will work through the night, so I generally end up with an
entry that is as follows;
OffLoad Time - LoadTime, 0:04 - 23:55 so the output is a really big negative
number which messes all aggregate calculations up.
Here is what I am using.
Cut Time: (DateDiff("n",Format([Loadtime],"Short
Time"),Format([Offloadtime],"Short Time")))

Any thoughts would be appreciated.
Thanks,
 
G

Guest

Why you are not using a full date and time value field, that way Access will
recognize that it's a new day?
 
G

Guest

I changed it to this;
Cut Time: (DateDiff("n",Format([Loadtime],"General
Date"),Format([Offloadtime],"General Date")))
And got the same output,
0:04 - 23:35 = -1,411
Any other thoughts?

Ofer Cohen said:
Why you are not using a full date and time value field, that way Access will
recognize that it's a new day?

--
Good Luck
BS"D


tjdaly said:
I am using a query to calculate machine time in a facility. I am subtracting
Load Time from Offload Time in general it works well except for on the night
shift where they will work through the night, so I generally end up with an
entry that is as follows;
OffLoad Time - LoadTime, 0:04 - 23:55 so the output is a really big negative
number which messes all aggregate calculations up.
Here is what I am using.
Cut Time: (DateDiff("n",Format([Loadtime],"Short
Time"),Format([Offloadtime],"Short Time")))

Any thoughts would be appreciated.
Thanks,
Click to expand...
Click to expand...
 
G

Guest

Do you have a date value stored in the field or just time, if you don't have
a date in it, it won't work.
Mybe you should consider saving the date also, and not just the time.
--
Good Luck
BS"D


tjdaly said:
I changed it to this;
Cut Time: (DateDiff("n",Format([Loadtime],"General
Date"),Format([Offloadtime],"General Date")))
And got the same output,
0:04 - 23:35 = -1,411
Any other thoughts?

Ofer Cohen said:
Why you are not using a full date and time value field, that way Access will
recognize that it's a new day?

--
Good Luck
BS"D


tjdaly said:
I am using a query to calculate machine time in a facility. I am subtracting
Load Time from Offload Time in general it works well except for on the night
shift where they will work through the night, so I generally end up with an
entry that is as follows;
OffLoad Time - LoadTime, 0:04 - 23:55 so the output is a really big negative
number which messes all aggregate calculations up.
Here is what I am using.
Cut Time: (DateDiff("n",Format([Loadtime],"Short
Time"),Format([Offloadtime],"Short Time")))

Any thoughts would be appreciated.
Thanks,
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I am capturing the daet with every record, in a separate date field?
What type of code would you propose?

Ofer Cohen said:
Do you have a date value stored in the field or just time, if you don't have
a date in it, it won't work.
Mybe you should consider saving the date also, and not just the time.
--
Good Luck
BS"D


tjdaly said:
I changed it to this;
Cut Time: (DateDiff("n",Format([Loadtime],"General
Date"),Format([Offloadtime],"General Date")))
And got the same output,
0:04 - 23:35 = -1,411
Any other thoughts?

Ofer Cohen said:
Why you are not using a full date and time value field, that way Access will
recognize that it's a new day?

--
Good Luck
BS"D


:

I am using a query to calculate machine time in a facility. I am subtracting
Load Time from Offload Time in general it works well except for on the night
shift where they will work through the night, so I generally end up with an
entry that is as follows;
OffLoad Time - LoadTime, 0:04 - 23:55 so the output is a really big negative
number which messes all aggregate calculations up.
Here is what I am using.
Cut Time: (DateDiff("n",Format([Loadtime],"Short
Time"),Format([Offloadtime],"Short Time")))

Any thoughts would be appreciated.
Thanks,
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Something like

SELECT DateDiff("n",[StartDate] & " " & [StartTime],[EndDate] & " " &
[EndTime]) AS DiffInMinutes, TableName.StartDate, TableName.StartTime,
TableName.EndDate, TableName.EndTime
FROM TableName

--
Good Luck
BS"D


tjdaly said:
I am capturing the daet with every record, in a separate date field?
What type of code would you propose?

Ofer Cohen said:
Do you have a date value stored in the field or just time, if you don't have
a date in it, it won't work.
Mybe you should consider saving the date also, and not just the time.
--
Good Luck
BS"D


tjdaly said:
I changed it to this;
Cut Time: (DateDiff("n",Format([Loadtime],"General
Date"),Format([Offloadtime],"General Date")))
And got the same output,
0:04 - 23:35 = -1,411
Any other thoughts?

:

Why you are not using a full date and time value field, that way Access will
recognize that it's a new day?

--
Good Luck
BS"D


:

I am using a query to calculate machine time in a facility. I am subtracting
Load Time from Offload Time in general it works well except for on the night
shift where they will work through the night, so I generally end up with an
entry that is as follows;
OffLoad Time - LoadTime, 0:04 - 23:55 so the output is a really big negative
number which messes all aggregate calculations up.
Here is what I am using.
Cut Time: (DateDiff("n",Format([Loadtime],"Short
Time"),Format([Offloadtime],"Short Time")))

Any thoughts would be appreciated.
Thanks,
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thank you soo much!!
It worked beautifully!

Ofer Cohen said:
Something like

SELECT DateDiff("n",[StartDate] & " " & [StartTime],[EndDate] & " " &
[EndTime]) AS DiffInMinutes, TableName.StartDate, TableName.StartTime,
TableName.EndDate, TableName.EndTime
FROM TableName

--
Good Luck
BS"D


tjdaly said:
I am capturing the daet with every record, in a separate date field?
What type of code would you propose?

Ofer Cohen said:
Do you have a date value stored in the field or just time, if you don't have
a date in it, it won't work.
Mybe you should consider saving the date also, and not just the time.
--
Good Luck
BS"D


:

I changed it to this;
Cut Time: (DateDiff("n",Format([Loadtime],"General
Date"),Format([Offloadtime],"General Date")))
And got the same output,
0:04 - 23:35 = -1,411
Any other thoughts?

:

Why you are not using a full date and time value field, that way Access will
recognize that it's a new day?

--
Good Luck
BS"D


:

I am using a query to calculate machine time in a facility. I am subtracting
Load Time from Offload Time in general it works well except for on the night
shift where they will work through the night, so I generally end up with an
entry that is as follows;
OffLoad Time - LoadTime, 0:04 - 23:55 so the output is a really big negative
number which messes all aggregate calculations up.
Here is what I am using.
Cut Time: (DateDiff("n",Format([Loadtime],"Short
Time"),Format([Offloadtime],"Short Time")))

Any thoughts would be appreciated.
Thanks,
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Date Time Group Math 3
short time calculations 4
calculation based on time 2
Using SUM with a short time format 4
Deleting time part of a Date, subtracting dates 3
Calculate Time in A Report 5
Excel Time Help 2
Calculating elapsed time w/DateDiff - negative minutes in some cal 3

Top