The Money Mustache Community
Learning, Sharing, and Teaching => Ask a Mustachian => Topic started by: LadyStache in Baja on October 13, 2017, 10:10:28 AM
-
I need to make a function that is dependent on date.
So if the value of column A is a date that falls between January and June, put "2" in Column B, otherwise put a 1.
Then I will multiply Column B by the values in Column C.
Maybe it is easier to do it without Column B. So if A is between January and June, multiply C by 2.
Ideas??
I'm technically using Google sheets, but the formulas are usually the same. How can I do this?!
-
Is there a chance you'll need to change the multipliers or the Jan-Jun range in the future?
And is this a specific year, or Jan-Jun of any year?
-
This one works in Excel:
=IF(AND(A1>DATE(2017,1,1),A1<DATE(2017,6,30)),1,2)
-
Anyone have a good tutorial for OpenOffice?
I'm old so a repetive style would help more.
-
What is the actual task you're trying to do and why? This could be an XY problem:
http://xyproblem.info/
If this is just a simple one-time thing then that IF statement would work.
-
=+INT( (MONTH(U68)-1) /6) + 1
this would be year independent
-
This one works in Excel:
=IF(AND(A1>DATE(2017,1,1),A1<DATE(2017,6,30)),1,2)
you could also simplify this further to
=IF(MONTH(A1)<7,2,1)
This way you will not have to update it every year.
Edit: If you wanted to exclude column B then your formula would be =IF(MONTH(A1)<7,2,1)*C1
-
This one works in Excel:
=IF(AND(A1>DATE(2017,1,1),A1<DATE(2017,6,30)),1,2)
you could also simplify this further to
=IF(MONTH(A1)<7,2,1)
This way you will not have to update it every year.
Edit: If you wanted to exclude column B then your formula would be =IF(MONTH(A1)<7,2,1)*C1
Yeah, that's much more elegant. Plus, you got the 2 and the 1 in the right order!
-
Thanks so much! I'll look into understanding what you guys wrote.
For those that asked, my goal is this:
Column B is the harvest date of certain vegetables. If the harvest falls between Jan and June of the year, then I want to double the amount I plant (because that's when our CSA is running).
So I'll have Column C which is the beds I'll plant.
So if I normally plant 2 beds of zucchini, during CSA season, I want to plant 4.
-
This one works in Excel:
=IF(AND(A1>DATE(2017,1,1),A1<DATE(2017,6,30)),1,2)
you could also simplify this further to
=IF(MONTH(A1)<7,2,1)
This way you will not have to update it every year.
Edit: If you wanted to exclude column B then your formula would be =IF(MONTH(A1)<7,2,1)*C1
Yeah, that's much more elegant. Plus, you got the 2 and the 1 in the right order!
This is awesome!!!! How do I tell it month<6 AND day < 10???
-
This one works in Excel:
=IF(AND(A1>DATE(2017,1,1),A1<DATE(2017,6,30)),1,2)
you could also simplify this further to
=IF(MONTH(A1)<7,2,1)
This way you will not have to update it every year.
Edit: If you wanted to exclude column B then your formula would be =IF(MONTH(A1)<7,2,1)*C1
Yeah, that's much more elegant. Plus, you got the 2 and the 1 in the right order!
This is awesome!!!! How do I tell it month<6 AND day < 10???
You would want to embed an IF statement within the TRUE part of the first IF statement. So: =IF(MONTH(A1)<7,IF(DAY(A1)<11,2,1),1))
-
If the harvest falls between Jan and June of the year, then I want to double the amount I plant (because that's when our CSA is running).
This is awesome!!!! How do I tell it month<6 AND day < 10???
Assuming you mean "if the harvest falls between 1-Jan and 10-June inclusive, then I want to double..." you could use
=IF(OR(MONTH(A1)<6,AND(MONTH(A1)=6,DAY(A1)<=10)),2,1)
-
This is great! I love taking out the mindless parts of my job!
It works! :D