Days Per Year When Range Crosses Jan 1

Question

I am trying to calculate the number of days that happen in each year when the range crosses a year boundary.

Examples are

handover_datetimereturn_datetime12/3/2022 11:301/21/2023 18:5812/5/2022 14:001/5/2023 14:0012/26/2022 8:3012/31/2022 17:5512/15/2022 13:301/16/2023 8:4612/10/2022 13:411/23/2023 12:00

I have been trying to use a flag to see when the return date is and then calculating using DateTimeDiff against Jan 1 or the relevant column, but nothing seems to be working perfectly.  I am attaching my sample data and the formulas I am trying to use.

Sample Workflow.yxmd

Sample Data.xlsx

jrlindem · Answer

@Timothy_Leber  - What is your expected difference in dates?  Meaning, can you elaborate more about how you want the computation to behave?

My guess, is that you're running into two things that influence your outcome:

1. Whether or not you want January 1 to be inclusive or not

2. Whether or not you want TIME to factor in

I put an illustration below to show how the time can impact the output in DAYS (below).  But maybe this isn't what you are asking?

Each formula is the same, but I'm using DateTime for the upper path and only Date for the lower path (converted using the Select Tool).

Hope the two responses here help you get to where you need to go!  -Jay

mauromorales · Answer

Just we have to use a formula with a dynamic data with years.
I hope to help you with the solution
.

009_challenge_days_diff.yxmd