Solved

Reporting based off of displayed text in cell.

Posted on 2013-12-01
5
152 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 48

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

707 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now