# Calculate based on date and time range

1. ## Calculate based on date and time range

Hi,

I have a file that is already totaling needed values based on an entered date range.
Now I would like to see if a way exist to narrow it by time of day also.
One problem is the time is recorded in two columns with the AM/PM in the second column.

new.jpg

Looking at the attached file can the formula in the green box be adjusted to accept a time range being added in the red boxes?
It would still need to work even if the time range is left blank.

I'd love to know how to chart this in 8 hour increments but that is another day.

Thanks for any suggestions.  Register To Reply

2. ## Re: Calculate based on date and time range

Hi,

Just extend the SUMIFS to include column D & the J3:K3 cells. i.e.

Formula:  `Please Login or Register  to view this content.`  Register To Reply

3. ## Re: Calculate based on date and time range

Very Nice.

How do I account for the AM/PM time periods? This data is over a 24 hour period. Right now column D has a 12 hour cycle with the time period (AM/PM) recorded in column E

So if a 1 AM - 6 AM range needs to be applied how would that be done?
Or do D & E need to be combined somehow? They are separated in the original data.  Register To Reply

4. ## Re: Calculate based on date and time range

=sumproduct((\$c\$5:\$c\$357+\$d\$5:\$d\$357+(\$d\$5:\$d\$357<=0.5)*(\$e\$5:\$e\$357="pm")*0.5>=\$h3+if(or(\$j3=0,\$k3=0),0,\$j3))*(\$c\$5:\$c\$357+\$d\$5:\$d\$357+(\$d\$5:\$d\$357<=0.5)*(\$e\$5:\$e\$357="pm")*0.5<=\$i3+if(or(\$j3=0,\$k3=0),1,\$k3))*(\$b\$5:\$b\$357)) ``Please Login or Register  to view this content.``  Register To Reply

5. ## Re: Calculate based on date and time range

That does seem to do the trick. Thanks to you both.

The second one is a bit more complex for me this early before my needed coffee :-)

So for the holy grail - do you have the magic mouse clicks that would turn this into a running chart, per day, broken into three 8 hour increments that begin at 7am?

Even if you don't the help is very appreciated.  Register To Reply

6. ## Re: Calculate based on date and time range

Hi,

Personally I'd add a helper column and join all three columns C:E
i.e. in F5 copied down

Formula:  `Please Login or Register  to view this content.`

Then your SUMIFS is simplified to

Formula:  `Please Login or Register  to view this content.`  Register To Reply

7. ## Re: Calculate based on date and time range

That does make edits easier.

On the copied down part - that seems to make the file huge since it pre-fills the entire column with the formula.
Does a way exist to auto fill the needed formula as needed? Such as when the needed data has been populated into previously unused cells the formula is added in then.  Register To Reply

8. ## Re: Calculate based on date and time range

Hi,

Yes indeed. A common solution is to hold the master formula above the data somewhere and then either copy it and paste it to the new rows, then copy the formula you've just put in the new rows and paste back as values.

Personally I just create a macro to perform the task above if the file is getting large solely because of the formulae.

However I'd be interested to hear how big the file is and how many rows the formula has been copied to. Are you sure it hasn't accidentally been copied to all 1 million + rows?  Register To Reply

9. ## Re: Calculate based on date and time range

The hope is to not have to come back and paste in formulas as the data grows.

Right now only one data source has been added. Once all is working as desired 7 more sources will be referenced so all can be consolidated.

File size is just over 10 megs. And sheepishly admitting that the formula has been pasted to countless cells - the entire column.
So, can you point to a posting that simply shows how to use a macro as mentioned? One that I suppose would look at a cell, determine that data is present, then add a formula to a cell in designated column on same row as new data that is recognized.  Register To Reply