MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Using Dates in IF Function

Posted by Sideshow Bob on November 17, 2000 3:16 PM

I'm making a model which will be a long list of items that will be purchased over the next year. I want to look at the dates when each item is ordered and put cost of the item in a cell that will keep a total of the items by quarter.
any ideas on how I can do this?

Posted by greg on November 17, 2000 4:19 PM


It is easier to use a date in an if function if you refer to it as a date serial number. An example would be 1/7/00 = 36708. So instead of putting the date in the if put the serial number corresponding to that date. Here is an example of an if/and statement in a cell that checks cell b1 to see if the date falls in the 3rd quarter and then takes the value in cell a1 if the date is in the third quarter of 2000. =IF(AND(B1<=36799,B1>=36708),A1,0). To do it that way you need four columns,corresponding to each quarter and then just sum them at the bottom and refer to the summed cells some where else for appearance.