If then query with dates

[Anonymous]

I am trying to write an if then statement in power query for a custom column named "2023 Days".  The purpose is to give me the total days a project is active in 2023.

I have the following data:

Actual Start Date	Planned Completion Date
12/1/2022	2/1/2024
11/1/2022	6/1/2023
10/1/2022	7/1/2023
9/1/2022	8/1/2023

The logic I would like to apply is this: if Planned Completion Date is greater than 12/31/2023, then 2023 Days = 365, otherwise 2023 Days = (Planned Completion Date - 1/1/2023).

Thank you in advance for your help and please let me know if there's anything else I can provide

[Mikelytics · Accepted solution]

Hi @Anonymous 

if [Planned Completion Date] > #date(2023,12,31) then 365 else [Planned Completion Date] - #date(2023,1,1)

Use formula

transform to whole number

Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.