Calculating data IF data is between two dates

[Hunter Childress]

Hi Experts,

I am trying to create a formula in Excel that calculates IF the day I am refrencing is in the current month, if not, then It should move to the next month.

For example, If today 1/1/2015, I want to reference between Jan 1 and before Feb 1, then if its true, use the data below is and divide by the days in the month. If false, then drag over to the correct data and do the same formula.

Please see my attached sample.

=IF(AND(B$9>=$B$3,B$9<$C$3),($B$4/(DAYS($C$3,$B$3)))

Thanks,

Hunter

 

[Somendra Misra]

Hi Hunter,

Can you fill in the data manually in your file. This will help users here to compare their results.

Regards,

 

[Hunter Childress]

Hi,

Attached is the updated data filled in manually.

Thanks,

Hunter

 

[NARAYANK991]

Hi ,

Is it something like this ?

Narayan

 

[Somendra Misra]

Hi,

Attached is the updated data filled in manually.

Thanks,

Hunter

Check Narayan Sir file. He had given the same result. I had this formula which is array entered.

=INDEX($A$4:$L$4,,MATCH(MONTH(A$7)&YEAR(A$7),MONTH($A$3:$L$3)&YEAR($A$3:$L$3),0))/(EOMONTH(A$7,0)-INDEX($A$3:$L$3,,MATCH(MONTH(A$7)&YEAR(A$7),MONTH($A$3:$L$3)&YEAR($A$3:$L$3),0))+1)

Enter with Ctrl+Shift+Enter.

Regards,

 

[Hunter Childress]

This is perfect! Thank you very much Narayan!

 

[Hunter Childress]

Sorry but when I click on a cell to view the formula, then select enter to close it out, it gives me #N/A. Any idea why?

Hunter

 

[Somendra Misra]

Don't press just Enter, Press Ctrl+Shift+Enter, as the formula is array formula.

Regards,

 

[NARAYANK991]

Hi ,

It is what is known as an array formula ; Excel will calculate it correctly only when you press CTRL SHIFT ENTER ( press all these 3 keys together ) , and not just ENTER.

Narayan

 

[Hunter Childress]

Ah! Thank you for your help! This is great!