Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I need to calculate the time difference. I have following table
TR | Registered Date | Triggering Date |
1 | Oct 1, 2018 11:07:59 PM | Oct 2, 2018 5:00:00 PM |
2 | Oct 1, 2018 11:08:03 PM | Oct 2, 2018 5:00:00 PM |
3 | Oct 1, 2018 11:08:02 PM | Oct 2, 2018 5:00:00 PM |
4 | Sep 28, 2018 11:08:03 AM | Sep 28, 2018 5:00:00 PM |
5 | Sep 28, 2018 11:08:03 AM | Sep 28, 2018 5:00:00 PM |
6 | Sep 28, 2018 11:08:03 AM | Sep 28, 2018 5:00:00 PM |
7 | Sep 28, 2018 11:08:06 PM | Oct 1, 2018 5:00:00 PM |
8 | Sep 28, 2018 11:08:06 PM | Oct 1, 2018 5:00:00 PM |
9 | Sep 28, 2018 11:08:06 PM | Oct 1, 2018 5:00:00 PM |
10 | Sep 28, 2018 11:08:06 PM | Oct 1, 2018 5:00:00 PM |
I have field "Registered Date" and according to time stamp of it i need to have Triggering Date. The calculation scenario is
1.If my Registered Date time is before the 12:00:00 PM then it should have Triggering Date of same day but at 05:00:00 PM
2. If my Registered Date time is after the 12:00:00 PM then it should have Triggering Date of next day at 05:00:00 PM
3. If my day is Friday and time is after 12:00:00 PM then it should have manual hold of next Monday 05:00:00 PM in this case in above table 28th Sep 2018 is Friday and time is after 12:00:00 PM so Triggering Date is 1st Oct 2018 05:00:00 PM.
How can i achieve this?
Thanks in advance.
How about this?
"TR Auto Hold Date/Time (local)", TimeStamp(If(Frac("TR Auto Hold Date/Time (local)") < 0.5, Floor("TR Auto Hold Date/Time (local)") + MakeTime (17), LastWorkDate(Floor("TR Auto Hold Date/Time (local)"), 2) + MakeTime(17)), 'MM/DD/YYYY hh:mm:ss TT') as "Triggering Date"
Try this
Table: LOAD TR, TimeStamp#([Registered Date], 'MMM D, YYYY hh:mm:ss TT') as [Registered Date], TimeStamp(If(Frac(TimeStamp#([Registered Date], 'MMM D, YYYY hh:mm:ss TT')) < 0.5, Floor(TimeStamp#([Registered Date], 'MMM D, YYYY hh:mm:ss TT')) + MakeTime (17), LastWorkDate(Floor(TimeStamp#([Registered Date], 'MMM D, YYYY hh:mm:ss TT')), 2) + MakeTime(17)), 'MMM D, YYYY hh:mm:ss TT') as [Triggering Date]; LOAD * INLINE [ TR, Registered Date 1, "Oct 1, 2018 11:07:59 PM" 2, "Oct 1, 2018 11:08:03 PM" 3, "Oct 1, 2018 11:08:02 PM" 4, "Sep 28, 2018 11:08:03 AM" 5, "Sep 28, 2018 11:08:03 AM" 6, "Sep 28, 2018 11:08:03 AM" 7, "Sep 28, 2018 11:08:06 PM" 8, "Sep 28, 2018 11:08:06 PM" 9, "Sep 28, 2018 11:08:06 PM" 10, "Sep 28, 2018 11:08:06 PM" ];
Hi Sunny,
Thanks for quick reply.
Really appreciate your efforts.
But my date format is "M/D/YYYY hh:mm:ss " which i changed in your expression but still it is showing null values.
Not exactly sure what you have done incorrectly, can you share an image of the script and the date field so that I can see the format?
Hi,
This is the script modified by me:
TimeStamp#("TR Auto Hold Date/Time (local)", 'MM/DD/YYYY hh:mm:ss TT') as "TR Auto Hold Date/Time (local)",
TimeStamp(If(Frac(TimeStamp#("TR Auto Hold Date/Time (local)", 'MM/DD/YYYY hh:mm:ss TT')) < 0.5,
Floor(TimeStamp#("TR Auto Hold Date/Time (local)", 'MM/DD/YYYY hh:mm:ss TT')) + MakeTime (17),
LastWorkDate(Floor(TimeStamp#("TR Auto Hold Date/Time (local)", 'MM/DD/YYYY hh:mm:ss TT')), 2) + MakeTime(17)), 'MM/DD/YYYY hh:mm:ss TT') as "Triggering Date"
Screenshot is also attached for format.
How about this?
"TR Auto Hold Date/Time (local)", TimeStamp(If(Frac("TR Auto Hold Date/Time (local)") < 0.5, Floor("TR Auto Hold Date/Time (local)") + MakeTime (17), LastWorkDate(Floor("TR Auto Hold Date/Time (local)"), 2) + MakeTime(17)), 'MM/DD/YYYY hh:mm:ss TT') as "Triggering Date"
Hi Sunny,
Thanks for your help.
It worked fine.