Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

DateDiff not calculating negative value 1

Status
Not open for further replies.
capronton

capronton

MIS
Jun 28, 2013
159
US
When {apc_correlated.sched_time} > {apc_correlated.close_date_time} I should get an "Early" value but I do not.
Can someone explain why DateDiff does not give me values less than 0?

If DateDiff ("s", {apc_correlated.sched_time},{apc_correlated.close_date_time} ) < 0 then "Early" else
If DateDiff ("s", {apc_correlated.sched_time},{apc_correlated.close_date_time} ) > 360 then "Late" else "OnTime
 
Sort by date Sort by votes
Are you sure that {apc_correlataed.sched_time} is a datetime field? Could it just be a time field?

Maybe show samples of the data and actual results.

-LB
 
Upvote 0 Downvote
Just tested this and datediff returned negative values as appropriate. Please show several examples of the two datetime fields and the corresponding results of your formula.

-LB
 
Upvote 0 Downvote
Are you sure {apc_correlated.close_date_time} has data and is not null? If null formula will fail.
Ian
 
Upvote 0 Downvote
Notice that the second row says "Late" when it should say "Early"
In fact throughout the entire report I get no "Early". Can someone explain?

Thanks for your help. This is puzzling.


"cTime" "schedTime" "earlyLate"
14:52:34 14:52:34 "OnTime"
14:56:58 15:00:00 "Late"
15:07:46 15:07:46 "OnTime"
15:11:21 15:11:21 "OnTime"
15:13:47 15:13:47 "OnTime"
15:22:33 15:22:33 "OnTime"
15:29:47 15:24:00 "Late"
15:29:47 15:28:00 "Late"
15:30:24 15:30:24 "OnTime"
15:43:07 15:43:07 "OnTime"
15:51:13 15:51:13 "OnTime"
16:20:53 16:20:00 "Late"
15:58:14 15:58:14 "OnTime"
16:21:26 16:21:26 "OnTime"
16:39:00 16:39:00 "OnTime"
16:45:54 16:45:54 "OnTime"
16:51:30 16:51:30 "OnTime"
17:01:04 17:01:04 "OnTime"
17:11:12 17:11:12 "OnTime"
17:13:40 17:13:40 "OnTime"
17:15:55 17:44:00 "Late"
17:16:49 17:16:49 "OnTime"
17:19:57 17:19:57 "OnTime"
17:21:52 17:21:52 "OnTime"
17:34:10 17:34:10 "OnTime"
17:38:16 17:38:16 "OnTime"
17:46:04 17:46:04 "OnTime"
18:28:37 18:12:00 "Late"
18:28:37 18:16:00 "Late"
18:04:40 18:04:40 "OnTime"
18:29:10 18:29:10 "OnTime"
18:33:33 18:33:33 "OnTime"
18:36:35 18:36:35 "OnTime"
19:20:39 19:08:00 "Late"
19:48:09 19:12:00 "Late"
19:48:09 19:40:00 "Late"
18:55:50 18:55:50 "OnTime"
19:21:44 19:21:44 "OnTime"
19:48:50 19:48:50 "OnTime"
19:52:20 19:52:20 "OnTime"
20:05:05 20:05:05 "OnTime"
20:10:19 20:32:00 "Late"
20:10:19 20:36:00 "Late"

 
Upvote 0 Downvote
Using the formula below, I get the data set below. The entire data set produces no "Early"

If DateDiff ("s", {apc_correlated.sched_time},{apc_correlated.close_date_time} ) < 0 then "Early" else
If DateDiff ("s", {apc_correlated.sched_time},{apc_correlated.close_date_time} ) > 360 then "Late" else "OnTime"

"cTime" "schedTime" "earlyLate"
14:52:34 14:52:34 "OnTime"
14:56:58 15:00:00 "Late"
15:07:46 15:07:46 "OnTime"
15:11:21 15:11:21 "OnTime"
15:13:47 15:13:47 "OnTime"
15:22:33 15:22:33 "OnTime"
15:29:47 15:24:00 "Late"
15:29:47 15:28:00 "Late"
15:30:24 15:30:24 "OnTime"
15:43:07 15:43:07 "OnTime"
15:51:13 15:51:13 "OnTime"
16:20:53 16:20:00 "Late"
15:58:14 15:58:14 "OnTime"
16:21:26 16:21:26 "OnTime"
16:39:00 16:39:00 "OnTime"
16:45:54 16:45:54 "OnTime"
16:51:30 16:51:30 "OnTime"
17:01:04 17:01:04 "OnTime"
17:11:12 17:11:12 "OnTime"
17:13:40 17:13:40 "OnTime"
17:15:55 17:44:00 "Late"
17:16:49 17:16:49 "OnTime"
17:19:57 17:19:57 "OnTime"
17:21:52 17:21:52 "OnTime"
17:34:10 17:34:10 "OnTime"
17:38:16 17:38:16 "OnTime"
17:46:04 17:46:04 "OnTime"
18:28:37 18:12:00 "Late"
18:28:37 18:16:00 "Late"
18:04:40 18:04:40 "OnTime"
18:29:10 18:29:10 "OnTime"
18:33:33 18:33:33 "OnTime"
18:36:35 18:36:35 "OnTime"
19:20:39 19:08:00 "Late"
19:48:09 19:12:00 "Late"
19:48:09 19:40:00 "Late"
18:55:50 18:55:50 "OnTime"
19:21:44 19:21:44 "OnTime"
19:48:50 19:48:50 "OnTime"
19:52:20 19:52:20 "OnTime"
20:05:05 20:05:05 "OnTime"
20:10:19 20:32:00 "Late"
20:10:19 20:36:00 "Late
 
Upvote 0 Downvote
One problem is that there is no date. These are just times. Your formula should contain two datetimes. If you dates and times are in separate fields, you need to combine them, as in:

Datediff("s",datetime({table.scheddate},{table.schedtime}),datetime({table.close_date},{table.close_time})

Try posting the dates for both schedule and close in each of the above rows.

-LB

 
Upvote 0 Downvote
Excellent suggestion! Please see dates for both schedule and close below.

I notice that some schedule dates are different. How can I modify the DateDiff formula to handle this?


"cTime" "schedTime" "earlyLate"
6/18/17 14:52 6/18/17 14:52 "OnTime"
6/18/17 14:56 5/30/17 15:00 "Late"
6/18/17 15:07 6/18/17 15:07 "OnTime"
6/18/17 15:11 6/18/17 15:11 "OnTime"
6/18/17 15:13 6/18/17 15:13 "OnTime"
6/18/17 15:22 6/18/17 15:22 "OnTime"
6/18/17 15:29 5/30/17 15:24 "Late"
6/18/17 15:29 5/30/17 15:28 "Late"
6/18/17 15:30 6/18/17 15:30 "OnTime"
6/18/17 15:43 6/18/17 15:43 "OnTime"
6/18/17 15:51 6/18/17 15:51 "OnTime"
6/18/17 16:20 5/30/17 16:20 "Late"
6/18/17 15:58 6/18/17 15:58 "OnTime"
6/18/17 16:21 6/18/17 16:21 "OnTime"
6/18/17 16:39 6/18/17 16:39 "OnTime"
6/18/17 16:45 6/18/17 16:45 "OnTime"
6/18/17 16:51 6/18/17 16:51 "OnTime"
6/18/17 17:01 6/18/17 17:01 "OnTime"
6/18/17 17:11 6/18/17 17:11 "OnTime"
6/18/17 17:13 6/18/17 17:13 "OnTime"
6/18/17 17:15 5/30/17 17:44 "Late"
6/18/17 17:16 6/18/17 17:16 "OnTime"
6/18/17 17:19 6/18/17 17:19 "OnTime"
6/18/17 17:21 6/18/17 17:21 "OnTime"
6/18/17 17:34 6/18/17 17:34 "OnTime"
6/18/17 17:38 6/18/17 17:38 "OnTime"
6/18/17 17:46 6/18/17 17:46 "OnTime"
6/18/17 18:28 5/30/17 18:12 "Late"
6/18/17 18:28 5/30/17 18:16 "Late"
6/18/17 18:04 6/18/17 18:04 "OnTime"
6/18/17 18:29 6/18/17 18:29 "OnTime"
6/18/17 18:33 6/18/17 18:33 "OnTime"
6/18/17 18:36 6/18/17 18:36 "OnTime"
6/18/17 19:20 5/30/17 19:08 "Late"
6/18/17 19:48 5/30/17 19:12 "Late"
6/18/17 19:48 5/30/17 19:40 "Late"
6/18/17 18:55 6/18/17 18:55 "OnTime"
6/18/17 19:21 6/18/17 19:21 "OnTime"
6/18/17 19:48 6/18/17 19:48 "OnTime"
6/18/17 19:52 6/18/17 19:52 "OnTime"
6/18/17 20:05 6/18/17 20:05 "OnTime"
6/18/17 20:10 5/30/17 20:32 "Late"
6/18/17 20:10 5/30/17 20:36 "Late
 
Upvote 0 Downvote
@capronton,

You have been posting here for some 4 years having started 47 threads but only having given one thank you via the little purple star???? Plz learn to show your appreciation for the benefit of all members.

And plz use TGML Post Options to properly post your table clearly for the benefit of all members...
[pre]
"cTime" "schedTime" "earlyLate"
6/18/17 14:52 6/18/17 14:52 "OnTime"
6/18/17 14:56 5/30/17 15:00 "Late"
6/18/17 15:07 6/18/17 15:07 "OnTime"
6/18/17 15:11 6/18/17 15:11 "OnTime"
6/18/17 15:13 6/18/17 15:13 "OnTime"
6/18/17 15:22 6/18/17 15:22 "OnTime"
6/18/17 15:29 5/30/17 15:24 "Late"
6/18/17 15:29 5/30/17 15:28 "Late"
6/18/17 15:30 6/18/17 15:30 "OnTime"
6/18/17 15:43 6/18/17 15:43 "OnTime"
6/18/17 15:51 6/18/17 15:51 "OnTime"
6/18/17 16:20 5/30/17 16:20 "Late"
6/18/17 15:58 6/18/17 15:58 "OnTime"
6/18/17 16:21 6/18/17 16:21 "OnTime"
6/18/17 16:39 6/18/17 16:39 "OnTime"
6/18/17 16:45 6/18/17 16:45 "OnTime"
6/18/17 16:51 6/18/17 16:51 "OnTime"
6/18/17 17:01 6/18/17 17:01 "OnTime"
6/18/17 17:11 6/18/17 17:11 "OnTime"
6/18/17 17:13 6/18/17 17:13 "OnTime"
6/18/17 17:15 5/30/17 17:44 "Late"
6/18/17 17:16 6/18/17 17:16 "OnTime"
6/18/17 17:19 6/18/17 17:19 "OnTime"
6/18/17 17:21 6/18/17 17:21 "OnTime"
6/18/17 17:34 6/18/17 17:34 "OnTime"
6/18/17 17:38 6/18/17 17:38 "OnTime"
6/18/17 17:46 6/18/17 17:46 "OnTime"
6/18/17 18:28 5/30/17 18:12 "Late"
6/18/17 18:28 5/30/17 18:16 "Late"
6/18/17 18:04 6/18/17 18:04 "OnTime"
6/18/17 18:29 6/18/17 18:29 "OnTime"
6/18/17 18:33 6/18/17 18:33 "OnTime"
6/18/17 18:36 6/18/17 18:36 "OnTime"
6/18/17 19:20 5/30/17 19:08 "Late"
6/18/17 19:48 5/30/17 19:12 "Late"
6/18/17 19:48 5/30/17 19:40 "Late"
6/18/17 18:55 6/18/17 18:55 "OnTime"
6/18/17 19:21 6/18/17 19:21 "OnTime"
6/18/17 19:48 6/18/17 19:48 "OnTime"
6/18/17 19:52 6/18/17 19:52 "OnTime"
6/18/17 20:05 6/18/17 20:05 "OnTime"
6/18/17 20:10 5/30/17 20:32 "Late"
6/18/17 20:10 5/30/17 20:36 "Late"
[/pre]

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Upvote 0 Downvote
I gave you the formula in my last post. Try that.

-LB
 
Upvote 0 Downvote
Thanks LB for all your help with this!

Skip I apologize for not using the purple star to say thank you, and I will use the purple star next time.

However, you should at least mention that I always say thank you in my message just as did above.

Thanks again to ALL for the help you given me on this site!
 
Upvote 0 Downvote
The stars are a good way for members to identify helpful posts. Yes, a "thank you" is appropriate, but the purple star is the indicator for members searching for good info.

Glad you got a satisfactory resolution.

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Upvote 0 Downvote
LB/Ibass,

The formula below is not working because the DateTime is not in two different fields. DateTime is only one field.

Datediff("s",datetime({table.scheddate},{table.schedtime}),datetime({table.close_date},{table.close_time})

"cTime" "schedTime" "earlyLate"
6/18/17 15:29 5/30/17 15:24 "Late"

Is there any way to subtract time and ignore the date in the example above?

For example 15:29 - 15:24

Thanks for any assistance.
 
Upvote 0 Downvote
If there is only one date field, then how do different dates appear in the same row? Place the fields in the detail section--how do they actually display? Are there three fields? Date and two time fields? If so, then show an example of how they display in the detail section. Do the crime and sched times appear in different rows (and appear null in the other row)? Be sure to label the fields exactly in your example.

-LB
 
Upvote 0 Downvote
There are two datetime fields "cTime" and "schedTime". I would like to ignore the "date value" in both datetime fields below and only subtract the "time values"
For example, how can I subtract "cTime" from "schedTime" and ignore the date-part.

On row 2 below, I only want to subtract 14:56 - 15:00
not 6/18/17 14:56 - 5/30/17 15:00

"cTime" [tab][tab][tab]"schedTime"
6/18/17 14:52 [tab]6/18/17 14:52
6/18/17 14:56 [tab]5/30/17 15:00
 
Upvote 0 Downvote
Cannot imagine a scenario where doing this would make sense, but:

Datediff("s",datetime(currentdate,time({table.scheddate})),datetime(currentdate,time({table.closedate})))

-LB
 
Upvote 0 Downvote
...and he's not gonna tell you what scenario does make sense.

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

POrlowski
  • Locked
  • Question
Calculating Sick Time
Replies
7
Views
201
POrlowski
POrlowski
D
  • Locked
  • Question
Running total from a subreport only showing last value
Replies
4
Views
383
doodledump
D
Tichaona
  • Locked
  • Question
Row numbering formula not working
Replies
4
Views
211
lbass
lbass
jlr123
  • Locked
  • Question
Boolean Formula = True/False Not Intended Value
Replies
3
Views
136
jlr123
jlr123
BradCustom
  • Locked
  • Question
Need help with setting a value to 0
Replies
0
Views
95
BradCustom
BradCustom

Part and Inventory Search

Sponsor

Back
Top