Allow a cell to equal a value if between two dates
 

Hi All,

Long time listener, first time caller!

I am trying to improve my home finances spreadsheet. What I would like to be able to do is automatically populate specific cells with an income that falls on a specific day of the month, as well as bills that are payed once per month.
Every month on the 21st, we get a child tax credit. Currently, my spreadsheet uses 26 columns, representing my biweekly salary income schedule. SO G2 has the date of my payday, and G4 has the date of the day just before the next paycheck. It would look like:

G H I
1
2 Jan 11 Jan 25 Feb 8
3 to to to
4 Jan 24 Feb 7 Feb 21
5

...and repeated to the end of the year.

I would like row 5 to represent the child tax credit income, any time the 21st of the month falls between (or on) the two dates in the column. In this scenario, it would be G5 and I5.

I hope this isn't too much of a headache!
Thanks in advance!

Allow a cell to equal a value if between two dates
 

Hi,

Am Mon, 16 Jul 2018 02:05:12 +0100 schrieb NBrugger:

G H I
1
2 Jan 11 Jan 25 Feb 8
3 to to to
4 Jan 24 Feb 7 Feb 21
5

..and repeated to the end of the year.

I would like row 5 to represent the child tax credit income, any time
the 21st of the month falls between (or on) the two dates in the column.
In this scenario, it would be G5 and I5.

try in G5:
=IF(AND(G2<=DATE(YEAR(G2),MONTH(G2),21),G4=DATE(Y EAR(G2),MONTH(G2),21)),AmountOfTax,"")
and drag it to the right.


Regards
Claus B.
--
Windows10
Office 2016

try in G5:
=IF(AND(G2<=DATE(YEAR(G2),MONTH(G2),21),G4=DATE(YE AR(G2),MONTH(G2),21)),AmountOfTax,"")
and drag it to the right.


Hi Claus
Thanks for the quick reply! Unfortunately, for some reason, it doesn't return the credit, but just a blank. I've tried massaging your formula myself, but with no luck!

Allow a cell to equal a value if between two dates
 

Hi,

Am Mon, 16 Jul 2018 12:41:21 +0100 schrieb NBrugger:

Thanks for the quick reply! Unfortunately, for some reason, it doesn't
return the credit, but just a blank. I've tried massaging your formula
myself, but with no luck!

what do you have in row 2 and row 4? Are the values real dates or are
they strings?


Regards
Claus B.
--
Windows10
Office 2016

Allow a cell to equal a value if between two dates
 

Hi,

Am Mon, 16 Jul 2018 13:51:56 +0200 schrieb Claus Busch:

what do you have in row 2 and row 4? Are the values real dates or are
they strings?

have a look:
https://1drv.ms/x/s!AqMiGBK2qniTgdBfMgPv1TKkdldUDQ

In row 2 and row 4 are real dates entered with a formula.


Regards
Claus B.
--
Windows10
Office 2016

what do you have in row 2 and row 4? Are the values real dates or are
they strings?

have a look:
https://1drv.ms/x/s!AqMiGBK2qniTgdBfMgPv1TKkdldUDQ

In row 2 and row 4 are real dates entered with a formula.



This works!! I greatly appreciate it!
I'm still not sure why the formula didn't recognize the dates I had. I actually set mine up the exact same way you had with adding 14 to the previous. But once I copied your sheet dates into mine, everything was good.

Again, thank you very much!
Cheers
Tom

Allow a cell to equal a value if between two dates
 

Hi Tom,

Am Mon, 16 Jul 2018 17:16:10 +0100 schrieb NBrugger:

This works!! I greatly appreciate it!
I'm still not sure why the formula didn't recognize the dates I had. I
actually set mine up the exact same way you had with adding 14 to the
previous. But once I copied your sheet dates into mine, everything was
good.

you are welcome. I'm always glad to help.


Regards
Claus B.
--
Windows10
Office 2016