Date comparison in excell

[bennie46]

I have a table that has 14 tabs. One for each of the months and 2 summary pages. On each of the months tabs I have data in cells G9:G18. On one of the summary pages I am trying to pull said data into it from the 12 months tabs, but only want the previous months and the current months data. In cell A1 of the summary tab I have the today function in place (=TODAY()) I am using cell A1 as a reference cell. I am trying to use embedded if statements to step through each month to determine if today's date is less then the last day of each month to determine what data is pulled. Below is an example of the formula that I am trying to use. The Date in cell A1 is 2/10/2016. I have tried this formula as it is shown, without the double quotes around the date, with single quotes around the date, used the DATE(,,) function, tried it with an & in front of the date and this formula always seems to return the information from the January tab. Please help

=IF(A1<="1/31/2016",January!G9,(IF(A1<="2/29/2016",February!G9,(IF(A1<="3/31/2016",March!G9,(IF(A1<="4/30/2016",April!G9,(IF(A1<="5/31/2016",May!G9,(IF(A1<="6/30/2016",June!G9,(IF(A1<="7/31/2016",July!G9,(IF(A1<="8/31/2016",August!G9,(IF(A1<="9/30/2016",September!G9,(IF(A1<="10/30/2016",October!G9,(IF(A1<="11/31/2016",November!G9,(IF(A1<=12/31/2016,December!G9,"")))))))))))))))))))))))

 

[Richard U]

Since you're using nested if statements, the first time it evaluates as true, you're fed that data. You need a macro that will use looping logic to return data for all the months you are interested in. You can then use that macro as a formula which would be MUCH easier to maintain.

Do you have a sample of what you want the output data to look like? Dummy data would be fine, I'm just trying to figure out what you are trying to do.

 

[bennie46]

Since you're using nested if statements, the first time it evaluates as true, you're fed that data. You need a macro that will use looping logic to return data for all the months you are interested in. You can then use that macro as a formula which would be MUCH easier to maintain.

Do you have a sample of what you want the output data to look like? Dummy data would be fine, I'm just trying to figure out what you are trying to do.

OK each of the months tabs contain the following data:

[TABLE="class: grid, width: 242"]
<colgroup><col span="2"></colgroup><tbody>[TR]
[TD]Total Samples[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Total Collections[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Total Not Collected[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Total Exceptions[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Todays Date:[/TD]
[TD="align: right"]2/12/2016[/TD]
[/TR]
[TR]
[TD]Start Date:[/TD]
[TD="align: right"]1/1/2016[/TD]
[/TR]
[TR]
[TD]End Date:[/TD]
[TD="align: right"]1/31/2016[/TD]
[/TR]
[TR]
[TD]Average per Day:[/TD]
[TD="align: right"]0.3[/TD]
[/TR]
[TR]
[TD]Job Status:[/TD]
[TD]On Track
[/TD]
[/TR]
</tbody>[/TABLE]


The data varies depending on what month it is pulled from and what the date currently is as it is a spreadsheet that gets updated on a daily basis. What I am trying to do as stated before is use the formula that I previously posted to pull this data from the current months tab and the previous months tab based on the current date. I am doing this to be able to automate some reporting. I have been able to get the nested if function to work in the past just not with dates. What I am trying to figure out is if there is something that I need to do differently with the if statement and the dates to make them work. It usually pulls only the month of January, or December never in between. So it seems like the date is not being recognized for some reason.