Value between dates

aartiano

Hi Everyone,

New smartsheet user here, I am trying to get a total cost between dates on a running construction schedule. I have different row-categories (Land acquisition, permitting, site work, construction, & selling) with columns showing start date, end date, and cost. All costs are positive, revenue (selling) is negative value. 

I am trying to total all costs between dates by month, for example what are the running costs for the month of April. The objective is to know what our total debt is at any month. 

Please see attached screenshot, I really appreciate someone's help!

 

Christopher Brown

Hi there,

I would create a "Helper" column that consists of a formula that assigns a month number for each row with a date. After you have that value, you can do SUMIF to calculate total dollars with a month value of 1, 2, 3 etc... (Jan, Feb, Mar) and so on. The formula options are categorized and the date functions have quite a bit to choose from. After you have your values, you can hide the helper columns to retain your original format. 

 

CB

L_123

=sumif([Start Date]:[Start Date],Month(@cell)=4,Cost:Cost)

 

Without knowing what the values are hidden by the indents, and without knowing what the columns represent, that's my best guess as to what you want.

aartiano

Hi CB, thanks so much for your help!

What happens if my time period lasts longer than a year, meaning I have April 2019 and April 2019 that I need to capture?

Alexandra

aartiano

Thank you so much for you help, maybe the attached screenshot helps?

Christopher Brown

I have had this come up for me too, but for different reasons. I believe you could do another helper column that accounts for the year and add that to your IF formula...(I assume). There is likely an elaborate formulaic way to accomplish all of it, but I like the prefer column approach. 

Paul Newcome

Since you are using a start and end date and there can be monthly and yearly overlap, you will need to breakdown how much of the project cost came in which month/year for each project. You would then need to sum those breakdowns by month/year to get your totals.

aartiano

UPDATE on what I came up with based on everyone's help

1. I added a "Cost Month" column with whatever date I wanted associated with the cost for a row (in most cases the start date)

2. Under the Cash In/Out category I made a row for each month and subcategories for "Cost" and "Total Previous Month"

3. For "Cost" I manually added all the costs that fell within the "Cost Month" (Ex. =Cost1+Cost2...etc)

4. I made the row for the month have the total of the children to make it a running total

5. I used this formula to get the "Month Year" format to correspond to the "Cost Month":

=IF(MONTH([Cost Month]328) = 1, "January ", IF(MONTH([Cost Month]328) = 2, "February ", IF(MONTH([Cost Month]328) = 3, "March ", IF(MONTH([Cost Month]328) = 4, "April ", IF(MONTH([Cost Month]328) = 5, "May ", IF(MONTH([Cost Month]328) = 6, "June ", IF(MONTH([Cost Month]328) = 7, "July ", IF(MONTH([Cost Month]328) = 8, "August ", IF(MONTH([Cost Month]328) = 9, "September ", IF(MONTH([Cost Month]328) = 10, "October ", IF(MONTH([Cost Month]328) = 11, "November ", IF(MONTH([Cost Month]328) = 12, "December ")))))))))))) + YEAR([Cost Month]328)

I accomplished having the running total correspond to the month it falls in even if I change the first start date linked to all others

It's not pretty in any way shape of form but it works.