Remember Me?

#1
July 16th 18, 02:05 AM
 Junior Member First recorded activity by ExcelBanter: Jul 2018 Posts: 3
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!

#2
July 16th 18, 09:20 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Apr 2011 Posts: 3,632
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
#3
July 16th 18, 12:41 PM
 Junior Member First recorded activity by ExcelBanter: Jul 2018 Posts: 3

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!
#4
July 16th 18, 12:51 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Apr 2011 Posts: 3,632
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
#5
July 16th 18, 01:11 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Apr 2011 Posts: 3,632
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

#6
July 16th 18, 05:16 PM
 Junior Member First recorded activity by ExcelBanter: Jul 2018 Posts: 3

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
#7
July 16th 18, 06:07 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Apr 2011 Posts: 3,632
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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Tigers00 Excel Worksheet Functions 2 July 12th 09 03:18 AM Amber Excel Worksheet Functions 8 November 5th 08 04:09 PM Claudia Excel Discussion (Misc queries) 1 September 1st 06 04:16 AM PeterArvidsson Excel Discussion (Misc queries) 1 April 21st 06 02:40 AM can I make cell yes equal 1, no equa Excel Discussion (Misc queries) 3 January 25th 05 09:57 PM

All times are GMT +1. The time now is 07:23 AM.