Need help with building out a sheet based on days remaining in month

Faheem Shameem

Hi All,

I am working to convert an excel file into smartsheets and trying to figure out where to start here (this is something that I inherited and the original owner of this file isn't around anymore so I'm kind of reverse engineering this). This is what the excel file looks like:

The formulas that I need to recreate are in the shaded cells, and reference the month columns they're in and the following month, with a formula like this:

(Cell X10) =MAX((Y$9-MAX(X$9,$J10))*$V10,0)

(Cell Y10) =MAX((Z$9-MAX(Y$9,$J10))*$V10,0)

So cell X10 references the cells for 'Jan 2021' and 'Feb 2021', while cell Y10 references 'Feb 2021' and 'Mar 2021'

In digging into the logic of the formula, I believe the intention is to find the number of days left in the month based on 'Scale Date' and apply the 'Daily' revenue to get the revenue per day for that month. I double checked the math for several of these cells and that does seem to be the case (Daily value is truncated so it can have e.g. $.0014 as the actual value)

So for cell X10 - because the scale date is on 1/2/2021 the value here would be the number of days left in Jan (inclusive of the scale date) so 30*Daily $. For cell Y10 - since this row already scaled in Jan, I am looking at the full number of days for Feb (28*Daily $) and the same for Mar (31*Daily $) and so on.

I don't think this formula can translate one to one based one how the file is structured (I don't think smartsheets would allow for this structure either) and I'm at a bit of a loss on where to start. Any ideas or help would be appreciated.

L_123

The main issue is getting the variable month end date then right?

=date(year(Jan$1),month(date(year(Jan$1),month(Jan$1),25)+7),1)-1

This will get you the last day of the month, even in december. Then you need to subtract your [scale date] to get the days between, and multiply the daily.

=(date(year(Jan$1),month(date(year(Jan$1),month(Jan$1),25)+7),1)-1 - [scale date]@row) * Daily@row

For the drag to the right, instead of finding the number of days in every month, could you simply subtract the items of the previous months? so from february onward it would be

=(date(year(Jan$1),month(date(year(Jan$1),month(Jan$1),25)+7),1)-1 - [scale date]@row) * Daily@row -sum($[Jan 2021]@row:[Jan 2021]@row)

Faheem Shameem

Hi L@123,

Thanks for responding. I tried inputting just the top formula you provided and am receiving an UNPARSEABLE error. Are any of these fields supposed to be references?

L_123

Jan would be your column name with January 1st of the year you want in the first row on said column, similar to what you have inside of excel. The only other reference is [scale date] which would be the column name of your scale date (column J in excel)

Faheem Shameem

Got it, I made the adjustments but am now getting an Invalid Data Type error. Both the column with the formula and the referenced columns are Text/Number, I'm wondering if it's because the formula itself has specific integers?

I'm also thinking if it's possible to use a helper column that calculates the number of days in the month and use that to possibly figure out if

a) scale date is within a given month

b) if not, then use the number of days for that month.

L_123

The formula can mix integers and dates. Data type error is only on what data is output from a formula, not what is contained within it. Do you have the column formatted as a date?

Faheem Shameem

I have the column with the formula formatted as Text/Number. When switching it to Date the error remains, however. I also have the column with (Jan) formatted as Text/Number, switching that to Date does not get rid of the error.

So currently my formula is:

=DATE(YEAR({jan date}), MONTH(DATE(YEAR({jan date}), MONTH({jan date}), 25) + 7), 1) - 1

with {jan date} being this specific cell