custom function for the day of the week
 

Hi ng

Trying to create a custom function to return the day of the week
User enters a date in col c

Col b should return the day (Fri, Sat, etc...)

Payroll schedule like this
Friday Day 1
Saturday Day 2
Sunday Day 3
Monday Day 4
Tuesday Day 5
Wednesday Day 6
Thursday Day 7

My custom function:
Function MyDayWeek(Day)
Dim MyDay As String

Select Case Day
Case 1
MyDay = "Sunday"
Case 2
MyDay = "Monday"
Case 3
MyDay = "Tuesday"
Case 4
MyDay = "Wednesday"
Case 5
MyDay = "Thursday"
Case 6
MyDay = "Friday"
Case 7
MyDay = "Saturday"
End Select
End Function

First I use xl internal function weekday to convert serial
=weekday(C2) : returns val of 6 on 6/25/04
I did not specify return type

6/25/04 was a Friday so I setup my Case Select construct to allow fo
this numbering method

Problem:
=MyDayWeek(WEEKDAY(C2)) returns 0

Where did I go wrong

--
Message posted from http://www.ExcelForum.com

custom function for the day of the week
 

in Col C just reference the cell with the data (assume B2). So in C2

=B2

select C2 and do Format=Cells=Number Tab, custom

type in the format dddd

That will display the day of the week.

or you can use the text function

=if(B2<"",Text(B2,"dddd"),"")

--
Regards,
Tom Ogilvy


"goss " wrote in message
...
Hi ng

Trying to create a custom function to return the day of the week
User enters a date in col c

Col b should return the day (Fri, Sat, etc...)

Payroll schedule like this
Friday Day 1
Saturday Day 2
Sunday Day 3
Monday Day 4
Tuesday Day 5
Wednesday Day 6
Thursday Day 7

My custom function:
Function MyDayWeek(Day)
Dim MyDay As String

Select Case Day
Case 1
MyDay = "Sunday"
Case 2
MyDay = "Monday"
Case 3
MyDay = "Tuesday"
Case 4
MyDay = "Wednesday"
Case 5
MyDay = "Thursday"
Case 6
MyDay = "Friday"
Case 7
MyDay = "Saturday"
End Select
End Function

First I use xl internal function weekday to convert serial
=weekday(C2) : returns val of 6 on 6/25/04
I did not specify return type

6/25/04 was a Friday so I setup my Case Select construct to allow for
this numbering method

Problem:
=MyDayWeek(WEEKDAY(C2)) returns 0

Where did I go wrong?


---
Message posted from http://www.ExcelForum.com/

custom function for the day of the week
 

Thanks Tom
Guess I should look for the easiest answer first
Of course, your solution worked

How could I change this format after input?
Ex: C1 awaits user input for a date
B1 (=C1) is formatted dddd
Currently displays Saturday which must be 0

I'd like to display "-" as in zero for accounting format if C=0
Once user enters date in C1 remaining dates in Col C should fill an
days in col B should appea

--
Message posted from http://www.ExcelForum.com

custom function for the day of the week
 

Use a custom number format like this

dddd;;-

the format is

positive number; negative number; zero; text

--
Regards,
Tom Ogilvy


"goss " wrote in message
...
Thanks Tom
Guess I should look for the easiest answer first
Of course, your solution worked

How could I change this format after input?
Ex: C1 awaits user input for a date
B1 (=C1) is formatted dddd
Currently displays Saturday which must be 0

I'd like to display "-" as in zero for accounting format if C=0
Once user enters date in C1 remaining dates in Col C should fill and
days in col B should appear


---
Message posted from http://www.ExcelForum.com/