Solved

Reporting based off of displayed text in cell.

Posted on 2013-12-01
5
178 Views
Last Modified: 2013-12-28
I have a list of Dates with the format:  "ddd - mmm dd, yyyy", which for example, displays as: "Fri - Nov 01, 2013" in the cell.

What I would like to do is to create an IF formula in another cell that reports based off the displayed text from the described cells above. I've begun by utilizing the following formula: =IF((SEARCH("Fri",H9)),"Yes", "No"). The problem is the text displayed in the cell may be, "Fri - Nov 01, 2013, but in the formula bar it is listed as 11/1/2013. If the cell is placed in any other format, it displays as a number "41579". How can I get the formula to search the cell (H9 in this case) for the text that is displayed, not the actual date or corresponding number associated with that date?
0
Comment
Question by:Humb13St3ps
5 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39688061
I think you would need VBA for that unless there is some new feature in 2013
0
 
LVL 50

Accepted Solution

by:
Rgonzo1971 earned 250 total points
ID: 39688139
Hi,

pls try

=IF("Fri"=LEFT(TEXT(H9,"ddd - mmmm dd, yyyy"),3),"Yes", "No")

Open in new window


or you can test Friday as 6th day of the week

=IF(WEEKDAY(H9)=6,"Yes", "No")

Open in new window


Regards
0
 
LVL 14

Assisted Solution

by:Faustulus
Faustulus earned 250 total points
ID: 39688585
Rgonzo1971, you were faster. That was just what I was thinking, but let me complete the thought.
Weekdays are numbered 1 (Sunday) to 7 (Saturday). You can also search your column looking for a date that represents a particular weekday.
=MATCH(6,WEEKDAY(H$1:H30),0)

Open in new window

This must be entered as an array formula. Confirm with Shift+Ctl+Enter. This particular formula would be in row 30 and it would look at the range up to and including the current row. You can define the look-up range differently depending upon your need.
The result will be a row number in column H where the value is first found. You can use this row number in conjunction with the INDEX function to return the date or return a another value in the same row. You can modify the formula to return something other than an error if no match is found or to return the last occurrence of a match instead of the first.

BTW, if such is your need, you can also extract the weekday from any of your dates with
=WEEKDAY(H300) which would return a number between 1 and 7 as first above mentioned which you can use to look for the same weekday elsewhere in your list employing the MATCH function provided.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39688598
To be able to get the value "as displayed" you can use a formula like this
=DisplayedText(H9)

and this UDF in the VBA window

Function DisplayedText(rng as range)
    DisplayedText = rng.text
end function

To enter the UDF in VBA
Right-click on the sheet tab name
Select View code
From the menu      Insert > Module
Paste this code in the VBA window
Close the VBA window
Enter the function formula in excel.
0
 

Author Closing Comment

by:Humb13St3ps
ID: 39744391
Thank you for the quick and accurate assistance; it was most appreciative. Please excuse my delay in awarding the points.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question