Datetimeadd function issue

[L_T]

Hi all, 

I have to create a payment schedule using start date/end date and number of payments during the period. 

The data are:

- Start Date: Nov 5, 2017 

- End Date: Mar 5, 2024

- 39 payments

Using these data, I can derive the each payment occurs every 60.842105 days. 

In Excel, I can add the above number to the start date and I precisely get to Mar 05, 2024 after 39 payments.
However, when I use the datetimeadd function in Alteryx (using exactly the same parameters), my last date would Feb 02, 2024. Below the differences.

Any idea why Alteryx does not return the same schedule as Excel? And what would be the best method to fix it?

Thanks in advance 

Alteryx	Excel
05/11/2017	05/11/2017
04/01/2018	04/01/2018
05/03/2018	06/03/2018
04/05/2018	06/05/2018
03/07/2018	06/07/2018
01/09/2018	05/09/2018
31/10/2018	05/11/2018
30/12/2018	04/01/2019
28/02/2019	06/03/2019
29/04/2019	06/05/2019
28/06/2019	06/07/2019
27/08/2019	05/09/2019
26/10/2019	05/11/2019
25/12/2019	04/01/2020
23/02/2020	05/03/2020
23/04/2020	05/05/2020
22/06/2020	05/07/2020
21/08/2020	04/09/2020
20/10/2020	04/11/2020
19/12/2020	04/01/2021
17/02/2021	05/03/2021
18/04/2021	05/05/2021
17/06/2021	05/07/2021
16/08/2021	04/09/2021
15/10/2021	04/11/2021
14/12/2021	04/01/2022
12/02/2022	05/03/2022
13/04/2022	05/05/2022
12/06/2022	05/07/2022
11/08/2022	04/09/2022
10/10/2022	04/11/2022
09/12/2022	04/01/2023
07/02/2023	05/03/2023
08/04/2023	05/05/2023
07/06/2023	05/07/2023
06/08/2023	04/09/2023
05/10/2023	04/11/2023
04/12/2023	04/01/2024
02/02/2024	05/03/2024

[mceleavey]

Hi @L_T ,

I have a question regarding the calculations.

The number of days between and including those two dates is 2313.

2313/39=59.307692

You arrive at the number 60.842105

Can you explain your calculations?

M.

[L_T]

Hi @mceleavey,

I used (# payments - 1) as the first payment is done on the start date. 

[mceleavey]

@L_T ,

ok, so that number is also slightly different to yours:

[mceleavey]

@L_T ,

Actually, I got that. It's (Count-1)/38 as the first day needs to be removed.

[L_T]

Hi @mceleavey 

That's weird - this is what I get, which is in line with Excel result

[mceleavey]

@L_T ,

Excel automatically applies the FLOOR function.

Try this:

floor(([Count]-1)/38)

Workflow attached.

M.

[L_T]

Thanks @mceleavey , 

Unfortunately, that does not solve the issue - the last date in your workflow is Feb 2, 2024 which is not equal to the Excel calculations (and the provided date).

[mceleavey]

@L_T ,

yeah, I've done a calculation and the days between each step in Excel are inconsistent:

I'll have another look soon.

M.

[mceleavey · Accepted solution]

@L_T ,

I've figured out the discrepancy, and it's an intriguing one. Excel is not actually using days in the calculation, but rather seconds, which allows you to throw the days over into 61 or 60 in a seemingly random pattern. 

I can't quite replicate this but I'm very close.

I've attached the workflow and by the end of the calculation, every date is right aside from the very last one, which is 34 seconds out.

Obviously, you'll need to convert everything back to date format, but it's close.

My thoughts are this is a rounding issue, so you may need to parse the datetime sections and do some rounding, for example to the nearest 30 minutes.

M.