+ Reply to Thread
Results 1 to 3 of 3

Using days of the week to compute work hours

  1. #1
    Registered User
    Join Date
    01-17-2008
    Posts
    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. #2
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,099
    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,"")
    oldchippy
    -------------


    Blessed are those who can give without remembering and take without forgetting

    If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.

    Click here >>> Top Excel links for beginners to Experts

    Forum Rules >>>Please don't forget to read these

  3. #3
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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