Formula issue - IF Statement between 2 dates

Jenny_ie

I have input a formula to calculate a monthly revenue figure for an annual subscription charge (i.e. divide the subscription charge by 12 between the dates 01/10/2018 - 30/09/2019. I used an =IF(AND...) formula however it is only picking up Oct - Dec of every year rather than Oct 2018 - Sept 2019. I can't figure out what is wrong, other than potentially date format but all date cells are linked to the same input sheet.

Paul Newcome

Are all of the columns a Date type column? If not, you will need to reference each portion of the dates in a DATE function and compare them that way.

Jenny_ie

Hi Paul, thanks for your response. When you say "reference each portion of the dates" in a DATE function, is that manually naming each part of the date in the formula? The dates per the row above the formula are text as I need those columns to have dates/calculations so will this cause an issue if my "Start" and "End" date are Date type columns? Essentially I am trying to determine the monthly release of a subscription over the subscription period; I thought putting the start and end date of the subscription, and using a formula to automatically pick up the relevant months in between would be the relatively straightforward but perhaps I am approaching it the wrong way.

Paul Newcome

Because your calculations (and therefore dates) are in a text/number type column, the dates cannot be used as dates without first converting them or at least pulling specific portions from within the text string that represents the date.

 

The syntax for the DATE function is this:

 

=DATE(yyyy, mm, dd)

 

where you replace each portion of the function with the appropriate numerical values.

.

dmy

So to convert the text string of "30/09/2018" into a usable date, you would need to use a RIGHT function to pull the year from the string, a MID function for the month, and a LEFT function for the day, each of which will need to be wrapped in it's own VALUE function to convert the text to a numerical value that can then finally be used within the date function.

 

So keeping the syntax in mind, we will break it down step by step how to convert "30/09/2018" into a usable date. It really isn't as complicated as it sounds by the way.

.

To pull the year DATE(yyyy, mm, dd), we simply need 

 

VALUE(RIGHT([Column Name]15, 4))

 

We take the right 4 characters and turn them into a number with the VALUE function.

 

For the month DATE(yyyy, mm, dd) we use

 

VALUE(MID([Column Name]15, 4, 2))

 

We use the MID function to move into the text string to the 4th character and pull 2 characters. VALUE function to convert it into a usable number and we're done with that portion.

 

All that's left is the day DATE(yyyy, mm, dd)

 

VALUE(LEFT([Column Name]15, 2))

 

Left function to pull the two leftmost characters and VALUE function to convert it to a number.

.

Now we just drop each portion into the appropriate part of the DATE function, and we're done.

.

=DATE(yyyy, mm, dd)

=DATE(VALUE(RIGHT([Column Name]15, 4)), mm, dd)

.

=DATE(yyyy, mm, dd)

=DATE(VALUE(RIGHT([Column Name]15, 4)), VALUE(MID([Column Name]15, 4, 2)), dd)

.

=DATE(yyyy, mm, dd)

=DATE(VALUE(RIGHT([Column Name]15, 4)), VALUE(MID([Column Name]15, 4, 2)), VALUE(LEFT([Column Name]15, 2)))

.

Now that you have a usable date, you can drop that into a formula.

 

=IF([Date Column]15 = date, do this)

=IF([Date Column]15 = DATE(VALUE(RIGHT([Column Name]15, 4)), VALUE(MID([Column Name]15, 4, 2)), VALUE(LEFT([Column Name]15, 2))), do this)

Jenny_ie

Thank you so much Paul - that worked a treat! I'm new to Smartsheet and trying to get the hang of it....without doubt I'll have a ton more questions :-)

Paul Newcome

Happy to help! 

 

Don't hesitate to ask away. There is most certainly a lot of knowledge to be found here in the Community.