YTD calculation - 01Apr-31Mar

Joseph Aloysias

Hi @Paul Newcome, this also shows "0" value

=COUNTIFS([Actual End Date]:[Actual End Date], @cell >= DATE(YEAR(TODAY()), 4, 1), [Actual End Date]:[Actual End Date], @cell <= DATE(YEAR(TODAY()), 3, 31), [Project Status]:[Project Status], OR(@cell = "Completed", @cell = "In Progress"))

Paul Newcome

=COUNTIFS([Actual End Date]:[Actual End Date], @cell >= DATE(2022, 4, 1), [Actual End Date]:[Actual End Date], @cell <= DATE(2023, 3, 31), [Project Status]:[Project Status], OR(@cell = "Completed", @cell = "In Progress"))

Previously you said the above was working fine, but then when you switched the hardcoded year in each of the DATE functions to YEAR(TODAY()) it stopped working?

Are you able to provide a screenshot of the formula in the actual sheet as opposed to a copy/paste?

Joseph Aloysias

Thank you for your response, please find the screenshot -

Yes its stopped working after I change to YEAR(TODAY()), I get "0" value

Paul Newcome

I see the problem. The second DATE function needs to be YEAR(TODAY()) + 1 so that it goes into next year.

Joseph Aloysias

You are awesome! it worked. Thank you so much 😊

Paul Newcome

Happy to help. 👍️

Joseph Aloysias

@Paul Newcome this formula shows "zero" value from Jan 1st 2023, Could you please help to fix this?

=COUNTIFS([Actual End Date]:[Actual End Date], @cell >= DATE(YEAR(TODAY()), 4, 1), [Actual End Date]:[Actual End Date], @cell <= DATE(YEAR(TODAY()) + 1, 3, 31), [Project Status]:[Project Status], OR(@cell = "Completed"))

Paul Newcome

@Domnic Victor Your formula is counting where the end date is between 1 April 2023 and 31 March 2024.

Joseph Aloysias

Yes @Paul Newcome, you are right. I used this formula to calculate YTD - its working now.

=COUNTIFS([Actual End Date]:[Actual End Date], @cell >= DATE(IF(MONTH(TODAY()) < 4, YEAR(TODAY()) - 1, YEAR(TODAY())), 4, 1), [Actual End Date]:[Actual End Date], @cell <= DATE(IF(MONTH(TODAY()) > 3, YEAR(TODAY()) + 1, YEAR(TODAY())), 3, 31), [Project Status]:[Project Status], AND(@cell = "Completed"))

Thank you for your support.