Compare Columns of Dates In A Formula

Copper Contributor

x

12 Replies

@Compl9x 

Hi Comp, here is an idea. Since you hide the date, use a formula that will put the amounts you are looking to sum based on >= to that date, then use those cells in your sumif formulas.

@Compl9x 

Hi Comp.

Your formula adds the amounts from a column (ex. AB) in the other tabs that meet your formulas criteria. If you create another column that will input the amounts from AB only if they are greater than or equal to a given date, then your sumif will only add amounts that are greater than that date.

On the other tabs you can set up a column that you can hide. Based on the date you are using, the formula will either resolve to blank, or the amount in cell ABx. IF(ab13>=TODAY(),AB13,""). TODAY() can be any date you specify, or even point to a date in another cell. You can write the formula once then drag it down. Better?

@SqueakySneakers 

I understand the idea and started implementing that into my worksheet. The formula I wrote in a Date (Hidden) column is 

=IF([@[ Date (Buy)]]="","",IF('Buying Checklist'!E28>[@[ Date (Buy)]],"",[@[ Date (Buy)]]))

 

Then I would have an IF before the SUMIF check for a value in these columns. However, how would I account for transactions not placed in the Buying Checklist. By doing this everything I ever purchased would also have to be put into the buying checklist. My goal is to be able to delete information from the checklist after it is entirely completed. 

@Compl9x 

The values for the original total amounts are still there in the original range. (AB). You can use the total sum in AB and use the qualified sum in the new range where needed. Simple math will also give you the difference. Aside from this, I will need a better understanding. My original goal was to help you to sum your range with only the dates that met the criteria.

I only plan on using the Checklist for say 50% of my purchases. With that being said, for it to work with the current solution, wouldn’t I have to input everything I bought? Besides from only counting the data after a specific date, I want to be able to delete data after it reaches 100% completion or just never have some info inserted at all. The entry pages were designed to have all the specifics. The checklist is temporarily to help get me there.

@Compl9x 

Since I cannot see your spreadsheet and reference it against what you are saying, being specific gets more difficult. Correct my thinking: Your original sumif formula calculated the total for a given product with regards to what was entered on the other tabs, correct? Your new => date formula filtered out those entries that did not meet the date criteria so what was left to add was only the entered information that met the date criteria, then filtered by the sumif equation adjusted to use the new range. If my remark is correct, I do not understand why everything is needed to be input. With regards to your 100% completion, do you have markers that denote an item is 100% complete so you can use that as a TRUE/FALSE marker in an equation that can set the value to $0, or do you delete it manually?

@SqueakySneakers 

 

You are completely correct about the SUMIF formula. However, either yourself or I am confused on how the date portion is going to work. I will attach a version of my spreadsheet without the hidden columns below in case I messed anything up.

 

But I'll try to re-explain my goal of this sheet. When I buy something, I detail all the information of the buy into the Skins/Consumables tabs. The buying checklist would be used to plan SOME of my buying choices. Not everything I buy will be planned/listed in the Buying checklist tab. I believe this is the issue for me. Something in the Skins/Consumables may be skipped over and I don't think I'm correctly writing that in Excel. Instead of skipping over a row, my dates are being compared incorrectly. 

 

When I mentioned I wanted to delete info in the Checklist I meant the following: I manually type how many I want. Excel would tell me how many I bought so far. There is a remaining amount which does the subtraction. Finally the far left will show a check mark when remaining equals 0. I would love the option to manually delete a row of my inputs if I decided to. Say I buy all the items, well they aren't needed to be bought anymore, so I can remove them from the checklist but leave there entries on the correct page.

 

You should be able to access/download it here: https://drive.google.com/file/d/19CTtqmPoTHZsfokLAzaQIZ2bPeEPLDli/view?usp=sharing

@Compl9x 

Ok Got it. It will help me if we can place values in it so I can see how it currently operates, and how you want it to. Can we place some items in each Skins/Consumables, then see how the buying checklist currently works? Also, I do not see the formula based on the date anywhere.

@SqueakySneakers 

The formula for creating the date is causing me issues. Every formula I type is giving me a blank cell/not working since there is another page. I think the problem, for me..., is comparing the new Hidden dates to whatever cell is next on the list... Don't know how to solve this. I unhided all the cells and input some sample data in. New link for the excel file is below. Everything on the right side of the Checklist can be typed into Item for sorting.

 

https://drive.google.com/file/d/1CFyv1O9glRqwAxhhBuwiK_nP2qgKGnpy/view?usp=sharing

 

 

@Compl9x 

Instead of putting a date in that cell, (hidden date) have the formula resolve the amount you want counted instead, if the conditions are met. In your sumif formula range, point it to the hidden date range. It will count what is there if it meets the conditions, and what is there only meets your date condition.

The hidden date formula will have to be a lookup formula looking at the buying checklist page, and have an iferror clause so it zeros out when no value is found.

I took a second look at the Buying Checklist. If the sumif is based on the "Estimated Start" date for all entries, then no lookup is needed. It can simply be referenced.
Ok, in cell F9 of the consumable tab I put =IF(E9<'Buying Checklist'!$K$3,"",W9). This will put nothing if the date (buy) is less than the date on the buying checklist. Drag it down. Then, change the sumif formula (amount bought-buying checklist), to point to column F instead of W for all instances and it seems to work. Do the same thing on the skins page.