Help converting Excel formula to Smartsheet

meb_0101

I imported an Excel file into Smartsheet, and am stuck trying to convert this Excel formula into a functioning Smartsheet one.

I know some of these functions are not available in Smartsheet. However, I've tried a lot of different formulas and am still having trouble. I've even tried using ChatGPT and it's proven to be unhelpful. This is the most recent formula AI suggested but it's still resulting in #INVALID DATA TYPE:

=IF(YEAR([Date]@row) = 2023, (MONTH(DATE(2023, 12, 31)) - MONTH([Date]@row) + 1 + (DAY(DATE(2023, 12, 31)) / DAY(DATE(2023, 12, 31))) - ((DAY([Date]@row) - 1) / DAY(DATE([Date]@row, MONTH([Date]@row), 1)))) * [Monthly $ Breakdown]@row, IF(YEAR([Date]@row) = 2024, [Total $]@row, 0))

These are what the Excel columns referenced in below formula are: AC1 = "2023", W = dates column, Y = $ (formula based column), X = $ (manually entered).

=IF(COUNTIF(AC$1,YEAR($W6)),(DATEDIF(EOMONTH($W6,0)+1,EOMONTH(CONCAT("12/31/",AC$1),0)+1,"m")+(DAY(CONCAT("12/31/",AC$1))/DAY(EOMONTH(CONCAT("12/31/",AC$1),0)))-((DAY($W6)-1)/DAY(EOMONTH($W6,0))))*$Y6,IF(COUNTIF(AC$1,YEAR($W6)+1),$X6,0))

prime_nathaniel

meb_0101

If your concern is EOMONTH the equivalent in SMAR is

=IF(MONTH(today()) = 12, DATE(YEAR(today()), 12, 31), DATE(YEAR(today()), MONTH(today()) + 1, 1) - 1)

Concatenate is just =cell or string + cell or string

Everything else has a counter part in SMAR.

Dates should be entered in ISO or via the date constructor date(year,month,day).

NickStaffordPM

Okay so a few things:

1. Is your Date column a Date column type?
2. try puting the year in your logical statement to begin the IFs within quotes, IE "2023" instead of 2023
3. You are adding/subtracting/dividing by DATE functions (MONTH, DAY). This is another potential cause of your INVALID DATATYPE. I am not sure Smartsheet is built to be add and subract different levels of measuring time

Maybe try using Smartsheet's GenAI to create a formula based on you deacribing hwat you are are looking for in natural language?

meb_0101

@NickStaffordPM

1. Yes
2. I've tried this, and does not appear to make a difference. Also, this portion of the formula operates correctly with or without the quotes.
3. I was concerned about this as well. It seems possible at least based on post community posts like this one.

I wish I could. The Smartsheet GenAI isn't available on our Enterprise plan due to security concerns.

meb_0101

@Paul Newcome any ideas? I found your comments on discussions about DATEDIF and EOMONTH helpful, but I'm have difficulty putting it all together properly for my above noted situation.

If you are able to review this, thank you in advance!

Paul Newcome

What exactly are you wanting the formula to do?

meb_0101

@Paul Newcome I want the formula to return the total money saved in each year (my post is the 2023 formula, but they're all similar) based on the Date, Total $, and Monthly $ Breakdown columns. I've included an example below. For the screenshot, I manually entered the correct $ amount in the 2023 & 2024 columns based on the Excel sheet.

These projects are always 12 months and the "Total $" column is always based on this time frame. In the below example, I want the 2023 column to return the $ amount saved in 2023 based on the date provided and the Total $. Same desired outcome for the 2024 column.

In Excel, this was achieved by via the formula provided in my OG post. However, I'm not sure if it's possible to achieve the same results with one formula in Smartsheet.

Thank you for your time and I appreciate your feedback if you have any suggestions.

Paul Newcome

What is the logic though? Is it the [Total $] divided by 365 to get a daily amount and then have the number of days added up for each year?

So in the above screenshot, it would be 31 December 2023 minus 3 January 2023 to get the total number of days within 2023. Then multiply that by (15,715 divided by 365)?

Then for the 2024 column it would be (3 January 2024 minus 1 January 2024) multiplied by (15,715 divided by 365)? Or would it calculate out to 2 January 2024 as the "end date"?

meb_0101

@Paul Newcome So far, I've been trying to replicate the Excel formula but truthfully I don't fully understand it (someone else created it & I'm not an expert with Excel formulas). I have not had success even when trying to sub the Smartsheet versions of DATEDIF and EOMONTH functions.

"Is it the [Total $] divided by 365 to get a daily amount and then have the number of days added up for each year?" This is one possibility, and I do think simpler than trying to replicate the Excel formula. When manually calculating, I get $15,628.89 which is close enough for this.

For the 2024 column, 3 Jan 2024 should be the end date.

Thank you for the idea!

Paul Newcome

Give this a try…

=IF(YEAR(Date@row) = 2023, DATE(2023, 12, 31) - Date@row, IF(YEAR(Date@row) + 1 = 2023, Date@row - DATE(YEAR(Date@row) + 1, 1, 1), 0)) * ([Total $] / 365)

For the 2024 column, update each instance of 2023 with 2024.

meb_0101

@Paul Newcome Thank you! This worked for the projects that start later in the year, but is not returning the correct total amount for projects that start near the beginning of the year.

Formulas that work for the projects starting in Q2 of a year:

For 2023 column (start date: 5/31/23): =IF(YEAR([XXX Date]@row) = 2023, DATE(2023, 12, 31) - [XXX Date]@row, IF(YEAR([XXX Date]@row) + 1 = 2023, [XXX Date]@row - DATE(YEAR([XXX Date]@row) + 1, 1, 1), 0)) * ([Total $]@row / 365). This formula returns $92,979 while the Excel formula returns $92,935.

For 2024 column (same start date; end date: 5/31/24): =IF(YEAR([XXX Date]@row) = 2023, [XXX End Date]@row - DATE(2024, 1, 1), IF(YEAR([XXX Date]@row) + 1 = 2024, [XXX Date]@row - DATE(YEAR([XXX Date]@row) + 1, 1, 1), 0)) * ([Total $]@row / 365). This formula returns $65,607 while the Excel formula returns $65,651.

Although the year calculations don't exactly match the Excel formulas, the Total $ matches.

When applying the same formulas to rows with projects that start 1/3/23, it's off by 1 days worth of $.

Paul Newcome

The formula for the 2024 column isn't correct. There should be no references to 2023. You basically want to take the 2023 formula and update every year to add 1.

Paul Newcome

The possible reason for the extra day's worth is that 2024 is a leap year. February 2024 had an extra day in it.