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!

  • Congratulations Mike Lewis 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

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
 
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
 
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
 
Are you sure {apc_correlated.close_date_time} has data and is not null? If null formula will fail.
Ian
 
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"

 
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
 
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

 
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
 
@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]
 
I gave you the formula in my last post. Try that.

-LB
 
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!
 
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]
 
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.
 
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
 
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
 
Cannot imagine a scenario where doing this would make sense, but:

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

-LB
 
...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]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top