Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Reporting based off of displayed text in cell.

Posted on 2013-12-01
5
Medium Priority
?
203 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 53

Accepted Solution

by:
Rgonzo1971 earned 1000 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 1000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
Quickbooks hosting can do wonders to your enterprise but considering the points elaborated in the article which will help you to better analyze the outcomes. So scan your business, its needs and then move to the new world of limitless benefits.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

782 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