Create a date condition and count days in date period

[MonaAlmutairi]

Hey everyone,

I'm seeking your support in creating a condition to include specific records in a table and then count the days in the period depening on a condition.

My condition is: I want the records that include days in "2023"

For example:

Employee	Start Date	End Date
1	01/15/2023	01/17/2023
2	12/28/2022	01/04/2023

I should give the rows #1 and #2 to be included. Then, I need to count only the days in "2023". See below:

Employee	Start Date	End Date	Days
1	01/15/2023	01/17/2023	3
2	12/28/2022	01/04/2023	4

Thank you :) 

[ShankerV · Accepted solution]

Hi @MonaAlmutairi 

Please use the below formula to achieve your result in the Formula tool.

IF DateTimeParse([Start Date],"%m/%d/%y")>="2023-01-01"
THEN
DateTimeDiff(DateTimeParse([End Date],"%m/%d/%y"), DateTimeParse([Start Date],"%m/%d/%y"),"days")+1
ELSE
DateTimeDiff(DateTimeParse([End Date],"%m/%d/%y"), "2023-01-01","days")+1
ENDIF

Many thanks

Shanker V

[MonaAlmutairi]

@ShankerV  You are a live saver!! Thank you!