### Author Topic: Excel wizards (or Google Sheets wizards)  (Read 2263 times)

• Pencil Stache
• Posts: 698
##### Excel wizards (or Google Sheets wizards)
« 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?!

#### MonkeyJenga

• Walrus Stache
• Posts: 8928
• Location: the woods
##### Re: Excel wizards (or Google Sheets wizards)
« Reply #1 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?

#### solon

• Handlebar Stache
• Posts: 1964
• Age: 1820
• Location: CO
##### Re: Excel wizards (or Google Sheets wizards)
« Reply #2 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)

#### BTDretire

• Magnum Stache
• Posts: 3077
##### Re: Excel wizards (or Google Sheets wizards)
« Reply #3 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.

#### marielle

• Pencil Stache
• Posts: 860
• Age: 28
• Location: South Carolina
##### Re: Excel wizards (or Google Sheets wizards)
« Reply #4 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.

#### ToeInTheWater

• Stubble
• Posts: 140
• Location: Central Indiana
##### Re: Excel wizards (or Google Sheets wizards)
« Reply #5 on: October 13, 2017, 10:44:48 AM »
=+INT(   (MONTH(U68)-1)   /6)   + 1

this would be year independent

#### Proud Foot

• Handlebar Stache
• Posts: 1150
##### Re: Excel wizards (or Google Sheets wizards)
« Reply #6 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
« Last Edit: October 13, 2017, 12:56:46 PM by Proud Foot »

#### solon

• Handlebar Stache
• Posts: 1964
• Age: 1820
• Location: CO
##### Re: Excel wizards (or Google Sheets wizards)
« Reply #7 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!

• Pencil Stache
• Posts: 698
##### Re: Excel wizards (or Google Sheets wizards)
« Reply #8 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.

• Pencil Stache
• Posts: 698
##### Re: Excel wizards (or Google Sheets wizards)
« Reply #9 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???

#### Proud Foot

• Handlebar Stache
• Posts: 1150
##### Re: Excel wizards (or Google Sheets wizards)
« Reply #10 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))

#### MDM

• Senior Mustachian
• Posts: 10666
##### Re: Excel wizards (or Google Sheets wizards)
« Reply #11 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)