First thing to figure out is if your dates are text or if they are numbers formatted by excel to display as dates. Its a very important distinction and you want the later as it allows you to use more of excels built in formulas. Some possible tests are
- =ISTEXT(A2)
- =ISNUMBER(A2)
- Change the cell formatting to general and see if the displayed contents change
For the sake of this solution, your dates will be assumed to be numbers. If they are not, there are plenty of questions/answers on how to convert text date to numbers/excel format.
UNDERSTANDING TIME/DATE IN EXCEL
Excel stores the date as an integer. It is the count of the number of days since January 0, 1900. So 1 is January 1st 1900. (I believe on make the reference year is 1905 but I could be wrong). So all dates in excel are simply integers with some special formatting applied to display them in some manner we are used to seeing. Time on the other had is stored a % of a day or if you prefer decimal of a day. 0.5 represent half a day or lunch. So if you need to just find the date from a mixed time date, you need to strip off just the integer portion. conversely if you just need the time, you just need to strip of the decimal portion.
Option 1 SUMPRODUCT
SUMPRODUCT
is an regular function that performs array like calculations. As such you want to limit range reference to roughly your data and not use full column/row references. If you wind up doing the later you can bog down your machine with excessive useless calculations on empty cells.
=SUMPRODUCT((INT($A$2:$A$12)=TODAY())*$B$2:$B$12)
Note TODAY()
is a volatile function. This means that it recalculates every time something on sheet changes even if it does not affect the function. A non-volatile function (most regular functions) only recalculate when a change that affects them occurs. As a resul,t if you copy a volatile function to a lot of cells you can wind up bogging down you computer every time something on a sheet is changed/entered.
Option 2 - SUMIFS
SUMIFS
is a regular formula that you can use full column references in without worry and my preferred option on how to deal with this situation. This formula takes the form of:
SUMIFS(Range to be summed, range for condition 1, Condition 1 check, ..., range for condition n, Condition n check)
so in your case you want to check the start of the day and end of the day so your formula will have to condition checks and should wind up looking something like the following.
=SUMIFS($B$2:$B$12,$A$2:$A$12,">="&TODAY(),$A$2:$A$12,"<"&TODAY()+1)
Check out SUMIF: https://exceljet.net/excel-functions/excel-sumif-function
– fixer1234 – 2019-03-24T21:30:17.343