Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Revenue per month Formula extending beyond 1 year
I have a sheet that I have created to forecast revenue per month based on forecasted schedules and dollars/project. Currently this just goes for 1 year, but with projects now starting late Q4 2017 and going into Q1 of 2018, I am needing to revise this formula to add a year definition. Would love if someone could take a look and give me some advice, I would appreciate it!
Attached are 2 screen shots  1 of the overview of the sheet and the other is the detail of the formula. This formula is in the Jan column for the 3873 Job.
Here is the formula:
=IFERROR(IF(AND(Jan$1 >= MONTH($ConStart6), Jan$1 <= MONTH($ConFin6)), $[Total Amount]6 / (MONTH($ConFin6)  MONTH($ConStart6) + 1)), "")
Any help would be appreciated.
Comments

Hello,
This is a neat challenge! FULL DISCLOSURE, I haven't been able to test this function out on my own. I may have added too many parenthesis. Regardless, this is what I have:
=IFERROR(IF(AND(DATE(Jan$2, MONTH(Jan$1), 1) >= $ConStart6, DATE(Jan$3, MONTH(Jan$1), 1) <= $ConFin6, $[Total Amount]6/ ((MONTH(DATE(Jan$2, MONTH($ConFin6), DAY($ConFin6))) + MONTH(DATE(Jan$3, MONTH($ConFin6), MONTH($ConFin6), DAY($ConFin6))))  MONTH($ConStart6)) + 1)), "")
If you receive an #UNPARSEABLE error, then fiddle with the parenthesis and doublecheck my cell references to make sure I typed everything correctly.
To break down your function sans IFERROR:
IF(AND(Jan$1 >= MONTH($ConStart6), Jan$1 <= MONTH($ConFin6)), ...
You might change this to be more centered around creating a date out of your Month and Years rows instead. You may also need to consider a years start and years end row, and ensure that they're all formatted the same way with a four digit year. The following example is based on you having a Year End row as row 3:
IF(AND(DATE(YEAR(Jan$2), MONTH(Jan$1), 1) >= $ConStart6, AND(DATE(YEAR(Jan$3), MONTH(Jan$1), 1) <= $ConFin6, ...
Now for the second part of your function that actually does the math:
..., $[Total Amount]6/ (MONTH($ConFin6)  MONTH($ConStart6) + 1))
This is a bit trickier, since MONTH will only return values 112. If you, hypothetically, have a project that starts 11/05/17 and ends 2/25/18, that's 4 months total. Here's what I came up with:
..., $[Total Amount]6/ ((MONTH(DATE(Jan$2, MONTH($ConFin6), DAY($ConFin6))) + MONTH(DATE(Jan$3, MONTH($ConFin6), DAY($ConFin6))))  MONTH($ConStart6)) + 1))
This SHOULD give you the total number of ConFin months and allow you to subtract the ConStart month from that. Again, I haven't been able to test this out, I'm purely going on testing it out in my head.
If this doesn't work out, let me know why and I might be able to think of something else. For reference, here's our complete functions list if you are inspired from my function: https://help.smartsheet.com/functions

Also to reiterate because I posted a wall of text:
The function I've made will require you to add an extra row with a "Year End" at the top so that you have a Year Start and Year End. If you don't want to do that, you could probably get away with changing my ...DATE(Jan$2, ... to reference $ConStart6 and Jan$3 to reference $ConFin6
Try it out!