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
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
Question by:Humb13St3ps
5 Comments

LVL 43

Expert Comment

ID: 39688061
I think you would need VBA for that unless there is some new feature in 2013
0

LVL 53

Accepted Solution

Rgonzo1971 earned 1000 total points
ID: 39688139
Hi,

pls try

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

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

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

Regards
0

LVL 14

Assisted Solution

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)
``````
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

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

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

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…
###### Suggested Courses
Course of the Month6 days, 19 hours left to enroll

#### 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.