Solved

Reporting based off of displayed text in cell.

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

679 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