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

Title: Excel wizards (or Google Sheets wizards)
Post 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?!

Title: Re: Excel wizards (or Google Sheets wizards)
Post by: MonkeyJenga on October 13, 2017, 10:25:42 AM
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?
Title: Re: Excel wizards (or Google Sheets wizards)
Post by: solon on October 13, 2017, 10:29:41 AM
This one works in Excel:

=IF(AND(A1>DATE(2017,1,1),A1<DATE(2017,6,30)),1,2)
Title: Re: Excel wizards (or Google Sheets wizards)
Post by: BTDretire on October 13, 2017, 10:37:00 AM
Anyone have a good tutorial for OpenOffice?
I'm old so a repetive style would help more.
Title: Re: Excel wizards (or Google Sheets wizards)
Post by: marielle on October 13, 2017, 10:37:37 AM
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.
Title: Re: Excel wizards (or Google Sheets wizards)
Post by: ToeInTheWater on October 13, 2017, 10:44:48 AM
=+INT(   (MONTH(U68)-1)   /6)   + 1

this would be year independent

Title: Re: Excel wizards (or Google Sheets wizards)
Post by: Proud Foot on October 13, 2017, 12:53:58 PM
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
Title: Re: Excel wizards (or Google Sheets wizards)
Post by: solon on October 13, 2017, 01:02:22 PM
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!
Title: Re: Excel wizards (or Google Sheets wizards)
Post by: LadyStache in Baja on October 22, 2017, 08:02:17 AM
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.
Title: Re: Excel wizards (or Google Sheets wizards)
Post by: LadyStache in Baja on October 27, 2017, 11:12:12 AM
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???
Title: Re: Excel wizards (or Google Sheets wizards)
Post by: Proud Foot on October 27, 2017, 01:39:47 PM
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))
Title: Re: Excel wizards (or Google Sheets wizards)
Post by: MDM on October 27, 2017, 03:57:55 PM
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)
Title: Re: Excel wizards (or Google Sheets wizards)
Post by: LadyStache in Baja on October 27, 2017, 04:27:23 PM
This is great! I love taking out the mindless parts of my job!

It works! :D