# Using days of the week to compute work hours

1. ## Using days of the week to compute work hours

I am trying to set up a timesheet that will compute 8 hours of scheduled work time for everyday of the week Monday thru Friday. I have set up the timesheet to fill out the current month's days of the month to equal the day of the week. For example, the current month of January 2008, the 17th falls on a Thursday and the timesheet will already do that. What I am trying to do is make it, in a seperate cell, fill in "8" for the hours scheduled for that day if the day falls on a Monday thru Friday, but if the day is a Sunday or Saturday, then it would either put a "0" or leave it blank. I have already set this up so that it will use the current month automatically. The formula that I use for example to calculate the first day of the month is: =DATEVALUE((MONTH(NOW())-0)&"/1/"&YEAR(NOW())) and then I use the custom number format of "d" to print "Tue" in the cell. Is there a better way of doing this and is it possible to have Excel schedule the 8 hours of work time just for the workdays?

2. Hi,

An alternative for the first day of the month with your date in A1 is

=DATE(YEAR(A1),MONTH(A1),1)

or if you want it all in one cell, then

=DATE(YEAR(TODAY()),MONTH(TODAY()),1)

And for your 8 hours for a weekday and blank for weekend

=IF(AND(WEEKDAY(A1)>1,WEEKDAY(A1)<7),8,"")

or again in one cell

=IF(AND(WEEKDAY(TODAY())>1,WEEKDAY(TODAY())<7),8,"")

3. To get the first of the current month

=TODAY()-DAY(TODAY())+1

If you have a date in B1 then to get 8 for a weekday or 0 otherwise

=(WEEKDAY(B1,2)<6)*8

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1