Skip to main content
Topics#FormulasBaird Straughan の質問
Baird Straughan が「#Formulas」で質問
2023年9月7日 21:13

Formula for fiscal day

I am looking for a way to calculate the day value of a given date within a fiscal year in order to be able to compare cumulative Oppty amounts over the fiscal year.  So if the FY starts on Sept 1, that date would be day 1, Dec. 31 would be day 122, and Jan.1 would be day 123 (etc.).  I'd also like this to factor in leap years (so no using 365 to calculate the day value), though I recognize the day of the year will differ by 1 on leap years.  What I've come up with so far relies on hand-entering the FY start month and/or day) and is this:

IF (

MONTH(CloseDate) < 9 ,

DAY(CloseDate) + 122,

DAY(CloseDate) - DAY (DATE(YEAR(CloseDate) , 09, 01) 

)

)

This formula should compare close date to a hard-wired fiscal start date of 9/1 and count days accordingly.  IF the close date is before September (or whatever month is used), the formula should count the day of the calendar year and add 122 days (again, a hard-wired value of the number of days from Sept 1 to Dec. 31).  If the close date is after August, it should calculate the fiscal day as the day of the close date minus the day of Sept 1.  I know using 9/1 may cause the day to be off by one but it seems like it otherwise should work.  And using 8/31 results in odd values.  

Unfortunately this just doesn't work at all.  It generates values like Oct. 13 = Fiscal Day 12 (so it's off by a month and a day).  January 1 becomes day 123, as it should, but subsequent dates are all a mess.  July 13 becomes day 136 (it should be more like 316) and March 31 becomes day 153 (it should be day 212).  

If possible I'd like to know what is wrong with my formula and how to fix it.  Why it doesn't work would be just about as helpful as a fix though.

Thanks so much!

 
2 件の回答
  1. Baird Straughan
    2023年9月13日 16:34
    Hi Ashwini,

    I tried this even though I don't fully understand what it's doing.  Unfortunately it appears to give me a result very similar to mine above.  Now, what I realize is this database has no FY set up so it's showing FY for the calendar year (the Fiscal Year field for reports is drawing from the FY that is set up in the database which is the same as Calendar Year (CY)), while the Fiscal Day field calculates for a fiscal year.  That explains why in my case FY 2021 shows days in March and July of CY 2021 as if they are in FY 2021, whereas they SHOULD be in FY 2020.  Note the way I calculated this depends on the FY definition calculating based on the start year, not the end year.  So 9/1/2021 would start FY 2021, instead of FY 2022.  I'll have to figure out the solution for a FY defined by the end date.

    Thanks for the help, but I'm back to the drawing board.  I can't change the FY of the current instance I'm working in so I'll have to build a dev instance where I can set the FY to another date and try the entire thing again. 
回答を記入...
0/9000