Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    May 2002
    Location
    Smithfield, Free State, South Africa
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts

    If Saturday then Zero (Excel 2000)

    I have a time sheet with the date displayed as Day day-month-year e.g. Monday 23-06-2003.
    I want to use a formula that will give a zero (0) in a cell if the day is a Saturday - it must give this irrespective of the date. So, all dates that are a Saturday (and in fact the Sunday as well) should display a 0. All other dates will have start and finish times in the cells.
    Any ideas???

  2. #2
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If Saturday then Zero (Excel 2000)

    One of:

    =IF(WEEKDAY(A1,2)<6,your-formula-bit,0)

    =(WEEKDAY(A1,2)<6)*your-formula-bit
    Microsoft MVP - Excel

  3. #3
    3 Star Lounger
    Join Date
    May 2002
    Location
    Smithfield, Free State, South Africa
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If Saturday then Zero (Excel 2000)

    Thanks for getting me thinking!

    I have found that =IF(WEEKDAY(B15)=1,0,IF(WEEKDAY(B15)=7,0,"")) ( where B15 is the date cell) works well in my case.
    Note: 1= Sunday and 7 = Saturday. If the cell contains neither (Monday to Friday) then the cell will be blank until I type a time in.

    This may help someone else as well.

    Regards

  4. #4
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If Saturday then Zero (Excel 2000)

    =IF(WEEKDAY(B15,2)<6,"",0)

    which is much shorter & faster.
    Microsoft MVP - Excel

Posting Permissions

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