Calculate Number of Days and Months to match Excel output

[Astro55]

I'm very new to Alteryx and trying to create a workflow. My data set contains start and end dates for various projects. I need to calculate the number of days and the number of months between the beginning and end dates of each project. 

Issue:
I am getting variances between the Excel and Alteryx calculations. While minor, they can add up to a material impact on my calculations. I figured out how to tweak the Alteryx formula for "number of days" to match my Excel output, but I can't figure out how to do the same for number of months.

Questions:

1. Can you please help me figure out how to get my Alteryx calculation to match my Excel calculation/ output? 

2. Is the "unedited" Alteryx calculation more accurate than my Excel calculation?

Details:

1. Formulas used to calculate the number of days per:
   • Excel: End Date - Start Date + 1
     
     
   • Alteryx: DateTimeDiff([Project End Date_Out],[Project Start Date_Out],'Days') + 1 
                    (Note: The "+1" is to get the Alteryx output to match Excel)
     
     
     
2. Formulas used to calculate the number of months per:
   • Excel: DATEDIF(0,*Number of days*,"M")
     
     
   • Alteryx: DateTimeDiff([Project End Date_Out],[Project Start Date_Out],'Months') 

[BRRLL99]

To get the Alteryx calculation to match your Excel calculation/output for the number of months, you could use the following formula in Alteryx:

Floor(DateTimeDiff([Project Start Date_Out],[Project End Date_Out],'day')/30)

This formula first calculates the difference in days between the start and end dates using DateTimeDiff, and then divides that number by 30 and rounds down using Floor to get the number of months. Note that this formula is an approximation and may not match Excel's calculation exactly for all date ranges.

It's difficult to say whether the unedited Alteryx calculation is more accurate than your Excel calculation without knowing the details of the data and the specific date ranges being used. However, the unedited Alteryx calculation simply calculates the difference in days between the start and end dates, which may not always accurately represent the number of months. The edited Alteryx calculation and the Excel calculation both use more nuanced calculations to approximate the number of months based on the number of days.

[binuacs]

@Astro55 Can you check your excel formula? try the formulas for Days  =DATEDIF(C4,D4,"d") and for months =DATEDIF(C4,D4,"m")

[Astro55]

Hi @binuacs - You bring up a good point, which actually helps me clarify my ask better, and then answers the second question in my original post (re: which formula is better).

The formula you suggest, and the Alteryx formula, are calculating the number of days between two dates, but (if my understanding is correct), both these formulas do not include the start and end date in the count. The Excel formula (end date) - (start date) + 1 includes the start/ end date in the count.  

What I need is Alteryx to include the start and end date when calculating the number of months between two dates.

Thank you so much for your help!

[apathetichell]

When you add 2 to 5 do you count 5 6 7?  you are adding 1 manually one in excel. add one manually in Alteryx. or start/end one day earlier.

[binuacs]

@Astro55  you can follow the same excel formula logic  in Alteryx

[Astro55]

Hi @BRRLL99 ,

Thank you so much for responding so quickly, and for the detailed explanation - I learned a new formula today! As you pointed out in your proposed solution, there are still errors I'm encountering with the new approach. 

I've also tried some of the solutions that users have suggested below. Attaching relevant data files here in case it helps. It feels like using the formula DateTimeDiff([End Date_Out],[Start Date_Out],'Months') is giving the least amount of errors for some reason - even if there ARE still errors.

[Astro55]

@apathetichell and @binuacs - thank you so much for your help and suggestions. I'm still getting errors though, and am attaching the relevant files below (I've tried all the methods noted above)

[binuacs]

@Astro55 What are the errors you are getting? I ran your workflow, I don't see any single error

[binuacs]

@Astro55 Ignore my previous post, when you said error i thought your workflow showing some error.