INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

DateDiff not calculating negative value

DateDiff not calculating negative value

(OP)
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"

RE: DateDiff not calculating negative value

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

RE: DateDiff not calculating negative value

(OP)
Yes, they are both datetime fields.

RE: DateDiff not calculating negative value

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

RE: DateDiff not calculating negative value

Are you sure {apc_correlated.close_date_time} has data and is not null? If null formula will fail.
Ian

RE: DateDiff not calculating negative value

(OP)
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"

RE: DateDiff not calculating negative value

(OP)
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"

RE: DateDiff not calculating negative value

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

RE: DateDiff not calculating negative value

(OP)
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"

RE: DateDiff not calculating negative value

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

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: DateDiff not calculating negative value

I gave you the formula in my last post. Try that.

-LB

RE: DateDiff not calculating negative value

(OP)
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!

RE: DateDiff not calculating negative value

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: DateDiff not calculating negative value

(OP)
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.

RE: DateDiff not calculating negative value

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

RE: DateDiff not calculating negative value

(OP)
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"             "schedTime"
6/18/17 14:52     6/18/17 14:52
6/18/17 14:56     5/30/17 15:00

RE: DateDiff not calculating negative value

Cannot imagine a scenario where doing this would make sense, but:

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

-LB

RE: DateDiff not calculating negative value

(OP)
Perfect! Thanks Ibass!

RE: DateDiff not calculating negative value

...and he's not gonna tell you what scenario does make sense.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close