Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
can I make cell "yes" equal 1, "no" equal 0 | Excel Discussion (Misc queries) | |||
If statements using less than or equal with dates | Excel Worksheet Functions | |||
How to exclude dates which equal SUNDAY from a fill series? | Excel Worksheet Functions | |||
match two columns (with equal dates) and their attached datasets | Excel Discussion (Misc queries) | |||
Compare two dates and if they are equal | Excel Discussion (Misc queries) |