Excel formula for week day work hours?


  1. Posts : 79
    WINDOWS 10
       #1

    Excel formula for week day work hours?


    So i have this excel formula to get the days of a month '=DATE(CONFIG!B3,CONFIG!D5,1)' I was wondering if there was a way to tag on a formula that based on if its a week day it would show the number of work hours (in this case 8) and if it was a weekend day it would show an X, any way to do this?

    basically first row days of the month, which I got covered
    second row right below each day the corresponding 8 or X respectively?

    so when I change the month in the formula I wouldn't need to put the values for each day

    any help is greatly appreciated!
      My Computer


  2. Posts : 11,582
    Windows 10 Home x64 Version 21H2 Build 19044.1776
       #2

    You can use the WeekDay function to return a number 1 to 7 then use an If <=5 for Monday - Friday to return 8 or X as required.

    When combining formulas, it can be easier to write them in separate cells referring each other and only to combine them into a single longer formula after you have debugged each one.

    It might be intentional but your code refers to the first of each month rather than any variable day within a month [unlike your Year & Month values which are picked up from cell values in your Config worksheet].

    Denis
      My Computer


  3. Posts : 79
    WINDOWS 10
    Thread Starter
       #3

    Try3 said:
    You can use the WeekDay function to return a number 1 to 7 then use an If <=5 for Monday - Friday to return 8 or X as required.

    When combining formulas, it can be easier to write them in separate cells referring each other and only to combine them into a single longer formula after you have debugged each one.

    It might be intentional but your code refers to the first of each month rather than any variable day within a month [unlike your Year & Month values which are picked up from cell values in your Config worksheet].

    Denis
    well I tried like you said , at least I think I did "=IF(WEEKDAY(F16)<=5,8,"X")" but its 1 day off, as in it shows the X value for the upper columns corresponding to Friday and Saturday, instead of the columns corresponding to Saturday and Sunday
      My Computer


  4. Posts : 11,582
    Windows 10 Home x64 Version 21H2 Build 19044.1776
       #4

    Sorry. I clearly did not pay attention when I tested it before posting.
    Sunday is day 1
    Monday is day 2
    Tuesday is day 3
    Wednesday is day 4
    Thursday is day 5
    Friday is day 6
    Saturday is day 7

    So the formula can be
    =IF(WEEKDAY(A3)>1,IF(WEEKDAY(A3)<=6,8,"X"),"X")

    Denis
      My Computer


  5. Posts : 79
    WINDOWS 10
    Thread Starter
       #5

    Try3 said:
    Sorry. I clearly did not pay attention when I tested it before posting.
    Sunday is day 1
    Monday is day 2
    Tuesday is day 3
    Wednesday is day 4
    Thursday is day 5
    Friday is day 6
    Saturday is day 7

    So the formula can be
    =IF(WEEKDAY(A3)>1,IF(WEEKDAY(A3)<=6,8,"X"),"X")

    Denis
    oh yes that works out great many thanks!

    any chance I could also trouble you for a conditional format rule, to change the color of the cells that turn out to be saturdays and sundays?
      My Computer


  6. Posts : 11,582
    Windows 10 Home x64 Version 21H2 Build 19044.1776
       #6

    Excel 2007 illustration. Yours might look a bit different.
    Excel formula for week day work hours?-conditinal-format-example.png

    Conditional formatting,
    New rule,
    Format only cells that contain ...
    Cell value - equal to - X
    Format

    I always start any conditional formatting from the New rule choice because I often get hopelessly confused by the other choices.

    I find the examples in this thread helpful
    Conditional formatting - multiple refs including to other cells - TenForums

    Denis
      My Computer


  7. Posts : 79
    WINDOWS 10
    Thread Starter
       #7

    Try3 said:
    Excel 2007 illustration. Yours might look a bit different.
    Excel formula for week day work hours?-conditinal-format-example.png

    Conditional formatting,
    New rule,
    Format only cells that contain ...
    Cell value - equal to - X
    Format …

    I always start any conditional formatting from the New rule choice because I often get hopelessly confused by the other choices.

    I find the examples in this thread helpful
    Conditional formatting - multiple refs including to other cells - TenForums

    Denis
    many thanks! you simply the best!
      My Computer


 

  Related Discussions
Our Sites
Site Links
About Us
Windows 10 Forums is an independent web site and has not been authorized, sponsored, or otherwise approved by Microsoft Corporation. "Windows 10" and related materials are trademarks of Microsoft Corp.

Designer Media Ltd
All times are GMT -5. The time now is 11:51.
Find Us




Windows 10 Forums