CODEWITHSUNDEEP
excelexcel-2003excel-formula
Lorbat
Lorbat

Reputation: 385

Weekday/ work day excel dates

How do you do this in excel without entering it all manually for the rest of the year? Its work days. JANUARY

03 04 05 06 09 10 11 12 13

T W T F M T W T F

So for January I need all the work days in numbers and in days in text like below: For January the first working day is 03 and that day is T(Tuesday). I need a formula to show on the first column 03 and then the 2nd row will show T for tuesday. I want this to continue for the rest of January. January

03

T

Upvotes: 0

Views: 739

Answers (1)

barry houdini
barry houdini

Reputation: 46341

If you don't mind using Analysis ToolPak functions you can try this approach:

First you need a list of holiday dates, e.g. 2nd Jan 2012, 6th April 2012. List these one per cell, perhaps in another worksheet and name that list holidays.

In A1 put the first of the month you want to see, e.g. 1/1/2012 (if you want it to default to current month always then use this formula =EOMONTH(TODAY(),-1)+1) - format that cell as mmm-yy

Now use this formula in A3 copied across to W3

=IF(WORKDAY($A1-1,COLUMNS($A3:A3),holidays)>EOMONTH($A1,0),"",WORKDAY($A1-1,COLUMNS($A3:A3),holidays))

format cells with custom format dd

and then in A4 use this formula copied across to W4

=LEFT(TEXT(A3,"ddd"))

Upvotes: 4

Related Questions