Value between dates

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!

Find more posts tagged with

Construction

Formulas

Project Management

Comments

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.

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…