Using formula in a Date column, then using SUMIF to total

Connor Reid

Hi all,

Relatively new to Smartsheets and I am looking for a bit of guidance here.

I am trying to put together a 'Resource Forecasting Tool' for our company, wherein the users can populate either an 'Personnel Discipline' or 'Equipment Item' to forecast, they then will enter the start and end date and the quantity required. 

Sheet Names are:

- Forecast Sheet;

- Summary Sheet.

The Forecast Sheet currently then uses the below formula to work out if the start and end dates fall within the week number and populate the cell with the quantity required is conditions are met:

=IF(OR($[Start Date]3 = [W1]$1, $[Start Date]3 > [W1]$1), $Qty3, IF($[End Date]3 > [W2]$1, $Qty3, IF(AND($[End Date]3 > [W1]$1, $[End Date]3 < [W2]$1), $Qty3, ""))) + ""

Now, as you will see I am having to use the ' + "" ' function to allow a formula to be used within a date formatted column. I had to do this, as I could not use a formula to compare the dates within a text formatted column. 

So, the formula works perfectly and all is going great, however now I want a formula on Summary Sheet to SUMIF all required items based on a pre-defined list of resources. For example, if in Forecacst Sheet the user wants to forecast 5 x technicians in Week 1, I need Summary Sheet to check use the below SUMIF to find out how many Technicians have been forecasted in Week 1.

=(SUMIFS({W1 SUM}, {W1 SUM}, >0, {DISCIPLINE}, $Resource3, {DISCIPLINE}, @cell = $Resource3))

Here is the problem, the above formula does not calculate anything due to the fact that all of the data being output from the Forecast Sheet is coming from Date formatted columns. Even if I add the ' + "" ' to the end of the formula it only responds with 0.

Anybody got any suggestions on how I can adapt this to work? 

TL;DR - How do I get formula driven data from a Date formatted column?

Paul Newcome

You will need to create a helper column and use the VALUE function to convert the text to numbers.

 

=VALUE([Date Column Name]@row)

 

You can then use the SUMIFS function on the helper column.

Connor Reid

Hi Paul,

Thanks for the response. I can confirm this works as required! 

Is it at all possible to encapsulate my initial formula within a VALUE() formula to save the need for a helper column? I am assuming not, as this column will still be formatted as a date column.

Thanks again,

Connor. 

Paul Newcome

Unfortunately not. The VALUE function wrapped around your entire formula would only ensure that the result of the formula is displayed as a number.

LGoller

Connor said: "Now, as you will see I am having to use the ' + "" ' function to allow a formula to be used within a date formatted column."

I'm not familiar with the '+"" ' function - does that work around columns that don't allow formulas? Thanks

 

Paul Newcome

It doesn't provide a workaround for columns that don't allow formulas (such as a contact type column). What it does is turns any numbers into a text string so that it can be used in a date type column or in place of a boolean value such as displaying "1" in a checkbox column instead of having the box checked. In the screenshot below, you will see an example. The left 2 columns represent the following in a checkbox column

 

=1

 

The right 2 columns (also checkbox) show what happens when 

 

=1 + ""

 

is used.