Any Excel Gurus here?

Post Reply
User avatar
Jez
Posts: 2590
Joined: Fri Jan 23, 2009 3:05 pm
Location: Out there, Somewhere...
Occupation: Thread Killer

Any Excel Gurus here?

#1

Post by Jez » Thu Aug 01, 2019 8:07 am

I've been scratching my head for the last several working hours trying to get a formula (or formulas) to work in Excel.

Here is the breakdown. I have a workbook.

On Sheet B, I have three columns (for sake of clarity, let's call them A, B, and C). These columns have Dates in them. These dates range from January 2019 through December 2020.

Column A is Overall Project Due Date.
Column B is Sustainability Period End
Column C is Validation Due Date

On Sheet A I have one column per month, each row is a specific project number. So, it would look like this:
Project NumberJan 19Feb 19Mar 19etc.
123456
---------------------------------------------------------
456784
You get the idea.

Is it possible to have the following type of IF/Then/Else statement?

If 'SheetB!A2' is between Jan 1, 2019 and Jan 31, 2019, then return A, else leave blank.
OR If 'SheetB!B2' is between Jan 1, 2019 and Jan 31, 2019, then return B, else leave blank.
OR If 'SheetB!C2' is between Jan 1, 2019 and Jan 31, 2019, then return C, else leave blank


ETA: What I have tried is this: IF(AND('Sheet B'!A2)<=DATE(2019,1,31),('Sheet B'!A2)>=DATE(2019,1,1))),"Y","N") as a test. It returns everything FALSE. If I use "OR" instead of "AND" it returns everything TRUE. Even if it's not. I was just testing to see if I could get it to work on just one column at least.

So, I pretty much need the formula to check three columns and tell me if the date is within that month. If it is, which column had a date in that month. If there is no date within that month, ignore it and leave it blank.

I have been trying for the last few working hours to get a formula to work, trying several different permutations, and nothing seems to want to work. Now if I need to consolidate the data to a hidden sheet to make things easier, I can/will. The only caveat is that the spreadsheet's data is fairly dynamic. Some of the dates might end up being updated. New project numbers are added, etc.

What they are looking for is a way to make an "at a glance" grid, with each column on Sheet B being assigned a specific color. That way, if they look at the grid, see a green square for project #45645 in Sept 2019, they know that the Validation Due Date for that project is in September. If it's Purple, they know it's the overall due date. That type of thing.

I really don't want to do this all manually, but will if necessary. I'm just looking for a way to automate this as much as possible. I am prepared for long, unwieldy formulas, which I think this mind end up being.

Any hints, tips, tricks, ideas would be greatly appreciated.
I have learned silence from the talkative, toleration from the intolerant, and kindness from the unkind; yet, strange, I am ungrateful to those teachers.

~Khalil Gibran

Dave at Sea
Posts: 506
Joined: Thu Apr 19, 2018 6:48 am
Location: Sand Sun Surf
Occupation: Busy

Re: Any Excel Gurus here?

#2

Post by Dave at Sea » Thu Aug 01, 2019 8:11 am

Have you considered a pivot table to put the data into the table format that you desire?

Dave at Sea
Posts: 506
Joined: Thu Apr 19, 2018 6:48 am
Location: Sand Sun Surf
Occupation: Busy

Re: Any Excel Gurus here?

#3

Post by Dave at Sea » Thu Aug 01, 2019 8:15 am

Creating a column for "Period" like
=month(A2) & "-"& year(A2)
give 6-2019 etc

User avatar
Jez
Posts: 2590
Joined: Fri Jan 23, 2009 3:05 pm
Location: Out there, Somewhere...
Occupation: Thread Killer

Re: Any Excel Gurus here?

#4

Post by Jez » Thu Aug 01, 2019 8:19 am

Dave at Sea wrote:
Thu Aug 01, 2019 8:11 am
Have you considered a pivot table to put the data into the table format that you desire?
Pivot Tables don't really give me what I'm looking for. I worked with them for two hours. There are too many blank cells for it to handle the data comfortably.
I have learned silence from the talkative, toleration from the intolerant, and kindness from the unkind; yet, strange, I am ungrateful to those teachers.

~Khalil Gibran

User avatar
Jez
Posts: 2590
Joined: Fri Jan 23, 2009 3:05 pm
Location: Out there, Somewhere...
Occupation: Thread Killer

Re: Any Excel Gurus here?

#5

Post by Jez » Thu Aug 01, 2019 8:19 am

Dave at Sea wrote:
Thu Aug 01, 2019 8:15 am
Creating a column for "Period" like
=month(A2) & "-"& year(A2)
give 6-2019 etc
Ok. That is an idea. That would make the data a little cleaner to work with, definitely.
I have learned silence from the talkative, toleration from the intolerant, and kindness from the unkind; yet, strange, I am ungrateful to those teachers.

~Khalil Gibran

Dave at Sea
Posts: 506
Joined: Thu Apr 19, 2018 6:48 am
Location: Sand Sun Surf
Occupation: Busy

Re: Any Excel Gurus here?

#6

Post by Dave at Sea » Thu Aug 01, 2019 8:38 am

For me - adding a Period is useful for converting a cell with date and/or date and time into a month and year.
I like the format
=year(A2)&"-"&month(A2)
as it then allows me in another tab to pivot table the data set to get

SumofStage
Project 2019-1 2019-2 2019-3 etc
1234 3
1235 2
1246 1
1251 1

If you also create a code for the stage of the project you can see at a glace where it is at
1 - yet to commence
2 - underway
3 - completed

User avatar
Northland10
Posts: 8325
Joined: Sat Jan 23, 2010 9:19 am
Location: Chicago area - North burbs

Re: Any Excel Gurus here?

#7

Post by Northland10 » Thu Aug 01, 2019 9:10 am

Remember, working with formulas/expressions/variables requires days of bad language and various trips to the store to replenish your stock of WTFs until suddenly you add parenthesis in the correct spot and "ah-ha" is heard across the land. 2 hours later you proclaim, "well shit" and the whole thing starts over.

Not that this ever happens to me.
North-land: of the family 10

UCC 1-106 Plural is Singular, Singular is Plural.

User avatar
Jez
Posts: 2590
Joined: Fri Jan 23, 2009 3:05 pm
Location: Out there, Somewhere...
Occupation: Thread Killer

Re: Any Excel Gurus here?

#8

Post by Jez » Thu Aug 01, 2019 9:20 am

Northland10 wrote:
Thu Aug 01, 2019 9:10 am
Remember, working with formulas/expressions/variables requires days of bad language and various trips to the store to replenish your stock of WTFs until suddenly you add parenthesis in the correct spot and "ah-ha" is heard across the land. 2 hours later you proclaim, "well shit" and the whole thing starts over.

Not that this ever happens to me.
yeah, that sounds about right. I'm fully stocked on the WTFs, but I might be running out of the "you gotta be kidding me". I'm ready to start programming a script to do it. I think that might actually be the easier way, since I'm wanting it to do three things in one cell.
I have learned silence from the talkative, toleration from the intolerant, and kindness from the unkind; yet, strange, I am ungrateful to those teachers.

~Khalil Gibran

User avatar
MN-Skeptic
Posts: 2812
Joined: Wed Jan 25, 2012 6:36 pm
Location: Twin Cities

Re: Any Excel Gurus here?

#9

Post by MN-Skeptic » Thu Aug 01, 2019 10:44 am

I'm confused by your:

IF(AND('Sheet B'!A2)<=DATE(2019,1,31),('Sheet B'!A2)>=DATE(2019,1,1))),"Y","N")

That has five left ( and seven ). Parenthesis have to be in pairs.

I may have a slightly different version of Excel, but try this:

=IF(AND(Sheet2!A2<=DATE(2019,1,31),Sheet2!A2>=DATE(2019,1,1)),"Y","N")

============================

Edit to add:

If you want the cell to return the date if it falls in that window, or a blank if it doesn't, then use this:

=IF(AND(Sheet2!A2<=DATE(2019,1,31),Sheet2!A2>=DATE(2019,1,1)),Sheet2!A2,"")
MAGA - Morons Are Governing America

Mr. Gneiss
Posts: 1795
Joined: Sat Jan 01, 2011 12:37 am

Re: Any Excel Gurus here?

#10

Post by Mr. Gneiss » Thu Aug 01, 2019 10:55 am

I have the same problem with parentheses and came up with the same syntax as MN-Skeptic.

User avatar
Jez
Posts: 2590
Joined: Fri Jan 23, 2009 3:05 pm
Location: Out there, Somewhere...
Occupation: Thread Killer

Re: Any Excel Gurus here?

#11

Post by Jez » Thu Aug 01, 2019 11:31 am

MN-Skeptic wrote:
Thu Aug 01, 2019 10:44 am
I'm confused by your:

IF(AND('Sheet B'!A2)<=DATE(2019,1,31),('Sheet B'!A2)>=DATE(2019,1,1))),"Y","N")

That has five left ( and seven ). Parenthesis have to be in pairs.
<snippy> :snippity: > :snippity: >>
Yeah, that was just bad transcription on my part. :oops: The spreadsheet and everything is on the work laptop, and because of the nature of the data in the spreadsheet, I cannot email it to myself. So... tried to type it while craning my neck looking sideways at my monitor. :)
I have learned silence from the talkative, toleration from the intolerant, and kindness from the unkind; yet, strange, I am ungrateful to those teachers.

~Khalil Gibran

User avatar
ObjectiveDoubter
Posts: 3475
Joined: Sun Sep 25, 2011 6:19 pm
Location: Hollywood (more or less)

Re: Any Excel Gurus here?

#12

Post by ObjectiveDoubter » Thu Aug 01, 2019 11:52 am

I just got to work, got my coffee, made my instant oatmeal and was reading around the 'Net, as I do every morning before I dive into "real" stuff. I'm intrigued.

I'll gonna try this one, BUT, as much as I'll play around with it and get back to you with a smile-worthy suggestion, the best answer I can give you is: Post it on MR EXCEL. If you're a heavy Excel user (and someone who uses nested IFs, ANDs, ORs and knows pivot tables usually is), you need to know the website, MR EXCEL. Over 12 years, I've probably posted 100 thorny questions and gotten solutions from its nerds to most, in less than 1 hour. Sometimes 4 or 5 solutions and sometimes in minutes.

https://www.mrexcel.com/forum/index.php

Love sharing this. :lovestruck: :lovestruck: :lovestruck:

User avatar
Northland10
Posts: 8325
Joined: Sat Jan 23, 2010 9:19 am
Location: Chicago area - North burbs

Re: Any Excel Gurus here?

#13

Post by Northland10 » Thu Aug 01, 2019 12:32 pm

Jez wrote:
Thu Aug 01, 2019 9:20 am
I'm fully stocked on the WTFs, but I might be running out of the "you gotta be kidding me".
I've personally decimated the entire Midwest supply of "Come On".
North-land: of the family 10

UCC 1-106 Plural is Singular, Singular is Plural.

User avatar
much ado
Posts: 1777
Joined: Mon Mar 23, 2009 12:56 am
Location: The Left Coast

Re: Any Excel Gurus here?

#14

Post by much ado » Thu Aug 01, 2019 12:44 pm

This works for me.

=IF(AND(D7>=DATEVALUE("1/1/2019"),D7<=DATEVALUE("1/31/2019")), "Yes"," No")

where cell D7 has the value being tested.

(Taking a break from trying to get t-SQL to call a .DLL I wrote.)

User avatar
Shizzle Popped
Posts: 1244
Joined: Tue Apr 09, 2013 3:54 pm
Location: South of Circle City
Occupation: Semi-Retired

Re: Any Excel Gurus here?

#15

Post by Shizzle Popped » Thu Aug 01, 2019 1:19 pm

I hate hard coding anything into a cell. Add a row to your spreadsheet with the first day of the month corresponding to the column:

Jan 19 Feb 19 Mar 19
1/1/2019 2/1/2019 3/1/2019

You can hide the second row when you're done.

Add a row to SheetB with the value you want to return (A, B, C).

Then concatenate month and year together from the dates on the two sheets and check if they're equal. Return the cell from SheetB if they are and display a blank if not. Something like this:

=IF((MONTH(B$1)&YEAR(B$1))=(MONTH(Sheet2!A3)&YEAR(Sheet2!A3)),Sheet2!A$1," ")
If Trump was a stopped clock he still wouldn't be right twice a day.

User avatar
Jez
Posts: 2590
Joined: Fri Jan 23, 2009 3:05 pm
Location: Out there, Somewhere...
Occupation: Thread Killer

Re: Any Excel Gurus here?

#16

Post by Jez » Fri Aug 02, 2019 7:57 am

Before starting a post on Mr Excel (Thanks for the pro-tip Northland!) I did some spelunking through their forums, in case someone had already posted on a similar issue.

Got me thinking... should I be using a VLOOKUP?
I have learned silence from the talkative, toleration from the intolerant, and kindness from the unkind; yet, strange, I am ungrateful to those teachers.

~Khalil Gibran

Lansdowne
Posts: 65
Joined: Sun Apr 02, 2017 4:00 am

Re: Any Excel Gurus here?

#17

Post by Lansdowne » Fri Aug 02, 2019 9:39 am

If you are still looking for help with your formula, you will need to quote the exact formula you have used, with the "(" and ")" all correct. Or tell us whether you have tried any of the suggestions made here. The logic of your original formula is sound, but the way you quoted it there should not be a ")" after both mentions of "SheetB!A2". Then you need to check the results of copying your formula to other cells: whether absolute references should be relative references or vice versa ("A2" versus "A$2" for example).

User avatar
dunstvangeet
Posts: 505
Joined: Wed Jul 01, 2009 11:53 am

Re: Any Excel Gurus here?

#18

Post by dunstvangeet » Fri Aug 02, 2019 9:57 am

What I'd be tempted to do is use the Month and Year functions to extract the month and year from the date.

So,

=IF(AND(MONTH('SheetB!A2')=1,YEAR('SheetB!A2')=2019),"Y","N")

MONTH is a formula which will return 1 for January, 2 for February, 3 for March, 4 for April, etc.

Year will just return the year that the date-code is in.

Also, make sure that your dates are actually stored as dates (which means numbers, with the date format) and not text.

That should work.

Post Reply

Return to “Computers & Internet”