• 460K Members
• 10.1K Online
• 558K Conversations

New Contributor

# Dates as numbers in formulas?

I use a spreadsheet to calculate fuel cost/economy and usage over the course of the year.  Individual columns show date (in date format), fuel cost, mileage, etc.  Then by simple division or multiplication I can calculate the cost per mile, economy per tankful (somewhat different depending on long or short haul trips).

My problem arises in the summation of the yearly costs.  At the bottom of data for each vehicle I do a summation.  The first column shows the date the vehicle was first used.  The second column shows that date in number format (for now by necessity).  The rest of the columns use a variation of the following formula.

=SUM IFS(E\$1:E\$149,\$A\$1:\$A\$149,">=42370",\$A\$1:\$A\$149,"<42736")

There is no space between the SUM and IFS. Was unable to post without the space.

Quite simply, I want to add the values in column 'E' if the values fall between 42370 and 42736.  42370 is 3/31/16 and 42736 is 1/1/17.  When I write the formula this way, everything works.  However, if I write the formula using B152 (the cell in which 42370 is located) and B153 (the corresponding cell for 42736), I get a an answer of 0.00 or some other null answer.  What am I doing wrong?  Over the course of time, for multiple vehicles, I have to rewrite every formula rather than just copying the formula down one row for the next year.

Thanks, in advance for any suggestions.

4 Replies

# Re: Dates as numbers in formulas?

However, if I write the formula using B152 (the cell in which 42370 is located) and B153 (the corresponding cell for 42736), I get a an answer of 0.00 or some other null answer.

Would you show us this formula?

# Re: Dates as numbers in formulas?

Your other formula should look like this:
=SUMIFS(\$E\$1:\$E\$149,
\$A\$1:\$A\$149,">="&\$B\$152,
\$A\$1:\$A\$149,"<"&\$B\$153)

# Re: Dates as numbers in formulas?

@Twifoo   Sorry that I did not respond sooner.  Just got the opportunity to see your solution and enter into my formula.  And it works!  Thanks.  If I can ask another question, just what does the '&' represent (for future reference)?

Again, thanks for the help.

# Re: Dates as numbers in formulas?

The ampersand (&) joins the comparative operator and the range reference.
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies