Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old July 16th 18, 02:05 AM
Junior Member
 
First recorded activity by ExcelBanter: Jul 2018
Posts: 3
Default 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!

  #2   Report Post  
Old 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,604
Default 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   Report Post  
Old July 16th 18, 12:41 PM
Junior Member
 
First recorded activity by ExcelBanter: Jul 2018
Posts: 3
Default

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   Report Post  
Old 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,604
Default 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   Report Post  
Old 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,604
Default 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   Report Post  
Old July 16th 18, 05:16 PM
Junior Member
 
First recorded activity by ExcelBanter: Jul 2018
Posts: 3
Default

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   Report Post  
Old 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,604
Default 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
If statements using less than or equal with dates Tigers00 Excel Worksheet Functions 2 July 12th 09 03:18 AM
How to exclude dates which equal SUNDAY from a fill series? Amber Excel Worksheet Functions 8 November 5th 08 03:09 PM
match two columns (with equal dates) and their attached datasets Claudia Excel Discussion (Misc queries) 1 September 1st 06 04:16 AM
Compare two dates and if they are equal PeterArvidsson Excel Discussion (Misc queries) 1 April 21st 06 02:40 AM
can I make cell "yes" equal 1, "no" equal 0 can I make cell yes equal 1, no equa Excel Discussion (Misc queries) 3 January 25th 05 08:57 PM


All times are GMT +1. The time now is 02:25 PM.

Powered by vBulletin® Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017