YTD calculation - 01Apr-31Mar

Joseph Aloysias - vSaaS Global

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 - vSaaS Global

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 - vSaaS Global

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

Paul Newcome

Happy to help. 👍️

Joseph Aloysias - vSaaS Global

@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 - vSaaS Global

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.