Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom 4 week periods | Excel Discussion (Misc queries) | |||
Help required on week function | Excel Worksheet Functions | |||
Day of week function needed | Excel Worksheet Functions | |||
Custom Date format ie. 01.01.05 W1 (W1 is week 1) | Excel Discussion (Misc queries) | |||
How do I make a Custom Datre Format ie 01.01.05 WK1 (WK is Week 1) sorry about previous post | Excel Discussion (Misc queries) |