date to day of week ignoring blank cells

  • Thread starter Thread starter emm8080
  • Start date Start date
E

emm8080

Hi

How can I covert dates to days of week eg 30/07/04 = Fri withou
converting blank cells at the end of the column.

Obviously, I can specify the column range to convert, but I wanted t
avoid this as it will be changing all the time. So I either need t
format the whole date column to "ddd" or use the weekday function - bu
this returns "Sat" for blank cells.

I then want to be able to count the number of times each day appears s
having the excess "Sat" cells at the end of my data is not helpful!

Thanks

Emm
 
-----Original Message-----
Hi

How can I covert dates to days of week eg 30/07/04 = Fri without
converting blank cells at the end of the column.

Obviously, I can specify the column range to convert, but I wanted to
avoid this as it will be changing all the time. So I either need to
format the whole date column to "ddd" or use the weekday function - but
this returns "Sat" for blank cells.

I then want to be able to count the number of times each day appears so
having the excess "Sat" cells at the end of my data is not helpful!

Thanks

Emma
Click to expand...
Hi Emma

Here's one way of manageing what you are after. Instead
of just using the weekday function, you can procede it
with an if statement. For example, assuming the date
colum is A the formula could end up looking like this =if
(A1="","",weekday(A1)). This would then return the
weekday for any cells with dates, but leave those without
blank.

Hope this helps

Cheers

Andy
 
Hi

Thanks guys!

Frank, I used your suggested function - is there any way of changin
the function so that it searches the column of dates and counts th
number of times eg Monday is represented (without actually listing th
days of the week).

The function I have so far, as you suggested, is:

=IF(Clicks!B2="","",TEXT(Clicks!$B2,"DDD"))

what I want to say is if B2="","", then count the number of time
Monday is represented and return that value.

Thx

Emm
 
Hi
could you post some example rows of your data (plain text please) and
the exavct formula you have used. This sounds like within the range you
have used a string exist
 
Hi Frank

This is my column - data is dd/mm/yyyy formatted to be displayed a
dd-mmm.

Column B

14-Jul
14-Jul
14-Jul
14-Jul
13-Jul
13-Jul
12-Jul
12-Jul
12-Jul
12-Jul
12-Jul
11-Jul
11-Jul
11-Jul
9-Jul
7-Jul
7-Jul
6-Jul
5-Jul
5-Jul
1-Jul

Currently, I have the following function to convert the date in to
day, but I want to avoid this middle step and just count the number o
occurences of each day of the week.

=IF(Clicks!B2="","",TEXT(Clicks!$B2,"DDD"))

I want a function to find the number of Mondays, Tuesdays etc etc
represented in this column.

Thanks

Emm
 
Something like this might work for you:

=SUMPRODUCT(--(B1:B21<>""),--(WEEKDAY(B1:B21,1)=3))

If you look at =Weekday() in help, you'll see that that you can specify that you
want it based on:

Sunday=1, Monday=2,..., Saturday=7.

So that "=3" says to count the number of Tuesdays.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Weekend dates conditional format 3
Day/Date/Week 3
Automatically Enter Formula in a Cell After Entering Data in Another Cell 0
Automatically Insert Dates For Week Off One Day Entry 7
Excel Concatenate with line breaks but ignore blank cells 3
Name of the Day 2
Automatically update a cell with a date based on anther cells date 2
SUMPRODUCT Problem 2

Back
Top