+ Reply to Thread
Results 1 to 6 of 6

calculating rostered days off

  1. #1
    Registered User
    Join Date
    04-05-2009
    Location
    Brisbane, Australia
    MS-Off Ver
    Office 365
    Posts
    71

    calculating rostered days off

    G'Day All,

    Please refer to the attached spreadsheet as it should hopefully help in my rambled explanation of what i'm trying to do below.

    I have a series of dates (not always beginning on a monday) to which I wish to calculate how many Sundays are being captured within that series. I don't always want to be calculating Sundays for every scenario so I'd like to be able to change this rostered day off and the entire function searches for that day, as opposed to a Sunday.

    Each workbook (not worksheet) will only have 1 rostered day off, so if 1 workbook has the variable set to Sunday that will be perfect.

    Any help in achieving this would be greatly appreciated!



    Regards,
    Xtopher
    Attached Files Attached Files
    Last edited by Xtopher; 03-05-2010 at 01:21 AM.
    Regards,
    Xtopher

  2. #2
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885

    Re: calculating rostered days off

    Hi Xtopher,

    The following array formula in D5 filled down will work for Sundays:

    =SUM(IF(WEEKDAY(B5-1+ROW(INDIRECT("1:"&(C5-B5)+1)))=1,1,0))

    This formula must be confirmed with CTRL+SHIFT+ENTER, not just ENTER. To get other days of the week, change the red 1 to 2 (Monday), 3 (Tuesday), etc.

  3. #3
    Registered User
    Join Date
    04-05-2009
    Location
    Brisbane, Australia
    MS-Off Ver
    Office 365
    Posts
    71

    Re: calculating rostered days off

    That is God like. Thank you very much!

    2 questions;

    The red 1 you have input in the example formula is Monday, and Tuesday, Wednesday etc are consecutive from that?

    Will the formula have to be "confirmed" as you put it with CTRL+SHIFT+ENTER everytime the spreadsheets used? Also, can you please explain the whole "confirming" forumla for me. That's new to me.

    Regards,
    Xtopher
    Last edited by Paul; 03-05-2010 at 01:20 AM.

  4. #4
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885

    Re: calculating rostered days off

    By default, weekday 1 = Sunday, 2 = Monday, 3 = Tuesday, 4 = Wednesday, 5 = Thursday, 6 = Friday and 7 = Saturday.

    "Confirming" a formula is the same as "completing" or "entering" a formula, but I don't want to use "entering" since you don't simply press Enter for array formulas. You have to hold down CTRL and SHIFT and then press ENTER. This is required whenever you TYPE or EDIT an array formula. Once you do this the first time, though, it will re-calculate in the future without you having to do that.

    Just remember if you ever go into the cell by double-clicking or pressing F2, or clicking in the formula bar while an array formula cell is selected, press CTRL+SHIFT+ENTER.

    When done properly, Excel automatically inserts braces around your formula, { }. Do not enter these yourself, as they will not simulate an array formula.

  5. #5
    Registered User
    Join Date
    04-05-2009
    Location
    Brisbane, Australia
    MS-Off Ver
    Office 365
    Posts
    71

    Re: calculating rostered days off

    Oh how stupid of me. Because of the nature of the formula (array formula) you must confirm it.

    Apologies, I may have just wasted your time... :/

    Thank you anyway. It is appreciated.
    Last edited by Paul; 03-05-2010 at 01:17 AM.

  6. #6
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885

    Re: calculating rostered days off

    No waste of time as long as you or anyone else searching the forum in the future can learn from it.

    If you wouldn't mind, please mark the thread as solved. To do so, click Edit in your original post, then click Go Advanced. Change the Title drop-down box from 'No prefix' to 'Solved' and click Save Changes.

    Thanks!

+ 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