Advanced Date Calculations is it possible

[Thorrrr]

Hi Guys
I am struggling to see if PE can actually do this please can you let me know if it possible?
If so how would you achieve this?

1. Order number (clipboard order number) was made on (user select calendar entry).
2. It was advertised with a (days dropdown) Mon to Fri business day delivery.
3. It should have been delivered on (take calendar entry and add on days dropdown to calculate business days Mon to Fri)
4. As you can see, we are (calculate the number of days late so take the delivery date and count to today’s date)
E.g 1
Line 1 16 Sept 2019
Line 2 5 Days
Line 3 16 Sept 2019 + 5 Business Days should return 23 Sept 2019
Line 4 If I send an email on Fri 27 Sept 2019 it should calculate 4 days late
E.g 2
Line 1 16 Sept 2019
Line 2 3 Days
Line 3 16 Sept 2019 + 3 Business Days should return 19 Sept 2019
Line 4 If I send an email on Fri 27 Sept 2019 it should calculate 8 days late

Thorrrr

[Peter Stotz]

@Thorrrr
Advaned date calculation - an example:
Step 1: Set a reference date
Step 2: Relative date = reference date + day difference x days
Step 3: If the relative date is a Sat or a Sun add 2 days or 1 day for the following weekday (monday)

Code: Select all

Step 1:
##reference_date: {#settemp -name ##reference_date -content {#datetime -f ddddd} -output}(* 
  day difference ##reference_date ± x days
*)
Step 2:
day difference: {#settemp -name ##day_difference -content {#input -head ##reference_date {#gettemp ##reference_date} ± how many days? -single -def +15 -required} -output}(* 
  ##relative_date
*)
##relative_date₁: {#settemp -name ##relative_date -content {#datetime -f ddd dd.mm.yyyy -value {#gettemp ##reference_date} -s {#gettemp ##day_difference}D -locale en-029} -output}(* 
  weekday check: If the relative date is a Sat or Sun, add 2 or 1 day for the following weekday (Monday).
*)
Step 3:
##relative_date₂: {#settemp -name ##relative_date -content {#datetime -f ddd dd.mm.yyyy -s {#check -text {#datetime -f ddd -value {#gettemp ##relative_date} -locale en-029} -case == Sat -output 2|== Sun -output 1}D -value {#gettemp ##relative_date} -locale en-029} -output}

Peter
Windows 10 - PhraseExpress v14.0.164

[Thorrrr]

Hello Peter
Can I thank you for your very knowledgable reply I am not an expert but having worked through your code i have most of it working accept Ste 3 it fails for me.

I have this so far but step 3 is not giving how many days late.

I think the issue is setting the reference date this has to be selected from a clander and will always be a past date never today's date. The essence of this phrase is to send an email reporting how late an item is and to work it out automatically.
Hope this makes sense. If you need further clarification please ask.

The return I get from the code below is


Hello Guys
Please, could you get one of your Logistics Team members to chase up the shipping status for this order?
The order was placed on 01/09/2019

delivery days advertised: +5
should have been delivered on: Fri 06.09.2019

##relative_date₂: Mon 30.09.2019

Code: Select all

Hello Guys
Please, could you get one of your Logistics Team members to chase up the shipping status for this order? 
The order was placed on {#settemp -name ##reference_date -content {#formcalendar -f ddddd -head Select date -variablename Calendar} -output}(* 
  day difference ##reference_date ± x days
*)

delivery days advertised: {#settemp -name ##day_difference -content {#input -head ##reference_date {#gettemp ##reference_date} ± how many days? -single -def +5 -required} -output}(* 
  ##relative_date
*)
should have been delivered on: {#settemp -name ##relative_date -content {#datetime -f ddd dd.mm.yyyy -value {#gettemp ##reference_date} -s {#gettemp ##day_difference}D -locale en-029} -output}(* 
  weekday check: If the relative date is a Sat or Sun, add 2 or 1 day for the following weekday (Monday).
*)
Days Late: {#settemp -name ##relative_date -content {#datetime -f ddd dd.mm.yyyy -s {#check -text {#datetime -f ddd -value {#gettemp ##relative_date} -locale en-029} -case == Sat -output 2|== Sun -output 1}D -value {#gettemp ##relative_date} -locale en-029} -output}

What am i doing wrong?

[Praia]

Hi,

It would seem that the date calculation does not lie working with two different formats of the date. I changed ##relative_date format and the final calculation, seems to work.

Code: Select all

Hello Guys
Please, could you get one of your Logistics Team members to chase up the shipping status for this order? 
The order was placed on {#settemp -name ##reference_date -content {#formcalendar -f dd.mm.yyyy -head Select date -variablename Calendar} -output}(* 
  day difference ##reference_date ± x days
*)

delivery days advertised: {#settemp -name ##day_difference -content {#input -head ##reference_date {#gettemp ##reference_date} ± how many days? -single -def +5 -required} -output}(* 
  ##relative_date
*)
should have been delivered on: {#settemp -name ##relative_date -content {#datetime -f dd.mm.yyyy -value {#gettemp ##reference_date} -locale en-029} -output}(* 
  weekday check: If the relative date is a Sat or Sun, add 2 or 1 day for the following weekday (Monday).
*)
Days Late: {#datecalc -date2 {#gettemp ##relative_date} -mode 2}

As a helping guide I have it output the variables, so I can actually see them all nicely together and this was my output:

Hello Guys
Please, could you get one of your Logistics Team members to chase up the shipping status for this order?
The order was placed on 01.10.2019

delivery days advertised: +5
should have been delivered on: 01.10.2019
Days Late: 0
Tue 01.10.2019

---------

Ref - 01.10.2019
Rel - Tue 01.10.2019
Day - +5

##################


Hello Guys
Please, could you get one of your Logistics Team members to chase up the shipping status for this order?
The order was placed on 23.09.2019

delivery days advertised: +5
should have been delivered on: 23.09.2019
Days Late: 8
Mon 23.09.2019

---------

Ref - 23.09.2019
Rel - Mon 23.09.2019
Day - +5

[Thorrrr]

Hello Praia
Thank you for your help but I am very confused.
Your output does not show any calculations you are showing Order Date 01.10.2019 with +5 delivery date which should show 08.10.2019
which would be 5 business days.
If I send an email on the 10.10.2019 it should show Days Late 2
If I send an email on the 11.10.2019 it should show Days Late 3

But only count business days Mon to Fri when calculating the delivery date

[Peter Stotz]

@Thorrrr, @Praia
a date specification with #datetime and the parameter -value must use the date in the system standard format.

–––––––––– This should work: ––––––––––

Hello Guys
Please, could you get one of your Logistics Team members to chase up the shipping status for this order?
The order was placed on {#settemp -name ##reference_date -content {#formcalendar -f dd.mm.yyyy -head Select date -locale en-029} -output}(*
day difference ##reference_date ± x days
*)

delivery days advertised: {#settemp -name ##day_difference -content {#input -head ##reference_date {#gettemp ##reference_date} ± how many days? -single -def +5 -required} -output}(*
##relative_date
*)
should have been delivered on: {#settemp -name ##relative_date -content {#datetime -f ddd dd.mm.yyyy -value {#gettemp ##reference_date} -s {#gettemp ##day_difference}D -locale en-029} -output}(*
weekday check: If the relative date is a Sat or Sun, add 2 days or 1 day for the following weekday (Monday).
*)
Expected delivery date: {#settemp -name ##relative_date -content {#datetime -f ddd dd.mm.yyyy -s {#check -text {#datetime -f ddd -value {#gettemp ##relative_date} -locale en-029} -case == Sat -output 2|== Sun -output 1}D -value {#gettemp ##relative_date} -locale en-029} -output}(*
*)
Number of days too late: {#calc {#datetime -a -value {#gettemp ##relative_date} -locale en-029}-{#datetime -a -value {#gettemp ##reference_date} -locale en-029} -round 0} days
––––––––––––––––––––

Peter
Windows 10 - PhraseExpress v14.0.164

[Thorrrr]

Hello Peter
If I run this I get this answer from your latest code which is wrong!
Hello Guys
Please, could you get one of your Logistics Team members to chase up the shipping status for this order?
The order was placed on 16.09.2019

delivery days advertised: +5
should have been delivered on: Sun 06.10.2019
Days Late: Tue 01.10.2019

It should say if sent today 1 Oct 2019

Hello Guys
Please, could you get one of your Logistics Team members to chase up the shipping status for this order?
The order was placed on 16.09.2019

delivery days advertised: 5 Business Days
should have been delivered on: Mon 23.09.2019
Days Late: 8

[Peter Stotz]

@Thorr
my example output:
Hello Guys
Please, could you get one of your Logistics Team members to chase up the shipping status for this order?
The order was placed on 16.09.2019

delivery days advertised: +5
should have been delivered on: Sat 21.09.2019
Expected delivery date: Mon 23.09.2019
Number of days too late: 7 days


What is the default date format on your system?
dd.mm.yyyy or mm/dd/yyyy or another?
Use it for the ##reference_date and settings with -value <date>

Peter
Windows 10 - PhraseExpress v14.0.164

[Thorrrr]

HI Peter

I am in the UK so my windows machine is as follows see https://1drv.ms/u/s!Ao6WCX2miTCJh9oOz9n ... A?e=o0HhT0

[Peter Stotz]

@Thorrrr
ok - test with the 'short format' dd/mm/yyyy e.g.
{#settemp -name ##reference_date -content {#formcalendar -f dd/mm/yyyy -head Select date -locale en-GB}
Peter
Windows 10 - PhraseExpress v14.0.164

[Praia]

Hi Thorrrr,

I reworked it a bit.
As Peter stated, adjust the dateformat if it does not work for you, change dd.mm.yyyy to dd/mm/yyyy (I tried with both and both work on my system)

I`m not sure "when" you are sending the mail, ie where that date comes from, I have used current date i.e today as the sending date.

Hence:
Ordered on - 23.09.2019
Day offset - +5
Due Day - 30.09.2019
Days Late: 2

or

Ordered on - 21.09.2019
Day offset - +5
Due Day - 27.09.2019
Days Late: 3

Sending date is today, 2.10.2019 (no input for this it takes the system current date)

gives me this output:

Hello Guys
Please, could you get one of your Logistics Team members to chase up the shipping status for this order?
The order was placed on 23.09.2019

delivery days advertised: +5
should have been delivered on: 30.09.2019
Days Late: 2

Here my new code:

Code: Select all

{#settemp -name ##orderd_on -content {#formcalendar -f dd/mm/yyyy -head Select ordered on date}}(*
*){#settemp -name ##day_offset -content {#formeditbox -text ± how many days? for delivery -def +5 -single -required}}(*
*){#settemp -name ##du_day -content {#datetime -f dd/mm/yyyy -s {#gettemp ##day_offset}w -value {#gettemp ##orderd_on}}}(*
*) This is ist for me a summary and if it works for you, delete it
Ordered on  - {#gettemp ##orderd_on}
Day offset - {#gettemp ##day_offset}
Due Day -  {#gettemp ##du_day}
---------------------
Hello Guys
Please, could you get one of your Logistics Team members to chase up the shipping status for this order? 
The order was placed on {#gettemp ##orderd_on}(

delivery days advertised: {#gettemp ##day_offset}
should have been delivered on: {#gettemp ##du_day}
Days Late: {#datecalc -date2 {#gettemp ##du_day} -mode 3}

[Praia]

PS. if you want to make the date show a litte bit more friendly use this to set the format:

Code: Select all

should have been delivered on: {#datetime -f dddddd -value {#gettemp ##du_day}}

would return in this format:
should have been delivered on: Wednesday, 25. September 2019

[Thorrrr]

Hello
OK I think we have about 85% working but if I run it today and leave the date it reports wrong

Hello Guys
Please, could you get one of your Logistics Team members to chase up the shipping status for this order?
The order was placed on 02/10/2019

The Costco website shows the shipping for this item is +5 business days.

This should have been delivered on 09 October 2019

The item is now 5 business days late!

I need this to update my customer and avoid cancellation of the order.
DaleAnditsgone

[Thorrrr]

Hi Guys
Any idea on how to fix the issue I am still having?
If i run the code today 8 Oct 2019 with 5-day delivery and i run it today it is not late.
But the code says 5 days late???

How can I fix this, please?

Hello Guys
Please, could you get one of your Logistics Team members to chase up the shipping status for this order?
The order was placed on 08/10/2019

The website shows the shipping for this item is +5 business days.

This should have been delivered on 15 October 2019

The item is now 5 business days late!

I need this to update my customer and avoid cancellation of the order.

[Peter Stotz]

Thorrrr,
please show us the latest code!
Peter
Windows 10 - PhraseExpress v14.0.164

[Thorrrr]

{#settemp -name ##orderd_on -content {#formcalendar -f dd/mm/yyyy -head Select ordered on date}}(*
*){#settemp -name ##day_offset -content {#formeditbox -text ± how many days? for delivery -def +5 -single -required}}(*
*){#settemp -name ##du_day -content {#datetime -f dd/mm/yyyy -s {#gettemp ##day_offset}w -value {#gettemp ##orderd_on}}}(*
*) Hello Guys
Please, could you get one of your Logistics Team members to chase up the shipping status for this order?
The order was placed on {#gettemp ##orderd_on}

The Costco website shows the shipping for this item is {#gettemp ##day_offset} business days.

This should have been delivered on {#datetime -f dddddd -value {#gettemp ##du_day}}

The item is now {#datecalc -date2 {#gettemp ##du_day} -mode 3} business days late!

I need this to update my customer and avoid cancellation of the order.

[Peter Stotz]

@Thorrrr
ok - thank you. This example works on my system:

Code: Select all

(* 20191009 • Order tracking Version 1 with #calendar/#input - without forms
*){#settemp -name ##ordered_on -content {#calendar -f dd.mm.yyyy -head Order placed on… -manual -locale en-GB}}(* 
*){#settemp -name ##day_offset -content {#input -head Ordered on {#gettemp ##ordered_on} • How many delivery time in days? -def +5 -single -required}}(* 
  delivery deadline ##du_day
*){#settemp -name ##du_day -content {#datetime -f dd.mm.yyyy -value {#gettemp ##ordered_on} -s {#gettemp ##day_offset}w -locale en-GB}}(* 
  Delivery time expired?
*){#check -text {#datetime -a -value {#gettemp ##du_day} -locale en-GB} -case < {#datetime -a -locale en-GB} -output Hello Guys
Please, could you get one of your Logistics Team members to chase up the shipping status for this order?
The order was placed on {#datetime -f  dddd, dd.mm.yyyy -value {#gettemp ##ordered_on} -locale en-GB}.

The Costco website shows the shipping for this item is {#gettemp ##day_offset} business days.

This should have been delivered on {#datetime -f  dddd, dd.mm.yyyy -value {#gettemp ##du_day} -locale en-GB}

The item is now {#datecalc -date2 {#gettemp ##du_day} -mode 3} business days late!
The item is now {#calc {#datetime -a -value {#gettemp ##du_day} -locale en-GB}-{#datetime -a -locale en-GB} -round 0} calendar days late!

I need this to update my customer and avoid cancellation of the order.(* 
  Delivery time not yet expired → Windows Notification
*) -else {#balloon -title Delivery time not yet expired: -text ##du_day: {#gettemp ##du_day} -onclick {#datetime -f ddd, dd.mm.yyyy -value {#gettemp ##du_day} -locale en-GB}}}

Shortened output examples on 09.10.2019
(1) ...
The order was placed on Tuesday, 01.10.2019.
The Costco website shows the shipping for this item is +5 business days.
This should have been delivered on Tuesday, 08.10.2019
The item is now 1 business days late!
The item is now -1 calendar days late!
...
(2) ...
The order was placed on Tuesday, 24.09.2019.
The Costco website shows the shipping for this item is 6 business days.
This should have been delivered on Wednesday, 02.10.2019
The item is now 5 business days late!
The item is now -7 calendar days late!
...
Peter
Windows 10 - PhraseExpress v14.0.164

[Thorrrr]

HI Peter

Not sure what is wrong but it fails at every level see https://1drv.ms/v/s!Ao6WCX2miTCJh9prdJZ ... Q?e=LYaWSp

My settings are UK in PE and on my Windows machine, none of it works here????

[Peter Stotz]

@Technical support / @all
The macro program works on my system ...
So what's going wrong here?
The regional date formats? The -value assignments? The shifts? ...

We are grateful for ideas ...
Peter Stotz
Windows 10 - PhraseExpress v14.0.164

[Technical Support]

You are using "dd.mm.yyyy" everywhere, which is not the same format as the used locale (en-GB). Replacing "dd.mm.yyyy" with "ddddd" should fix the problem.