Function RRS feed

  • Question

  • Hi : I am trying to create a function where based on the input of how many work hours each day (M-F) it calculates the proportionately the total number of hours for each financial year. Each full-time officer works 2008.514 hours in one financial year (M-F excl Public Holidays). Input also needs to be made for Start and End Date.

    Any ideas would be greatly appreciated. Thanks

    Parameterized Query

    Friday, June 14, 2019 5:49 AM


All replies

  • Are you wanting to do this in native Excel formulas, or Power Query?


    Friday, June 14, 2019 7:04 PM
  • Hi Ehren:

    Preferably both in native Excel language and PQ. There is a typo in the first post "Start Date" should read as "01/07/2018" 


    Friday, June 14, 2019 9:02 PM
  • Hi M.Awal,

    you're chances to get an answer here will rise if you provide sample data with the input and desired output and a (pseudocode) description of the calculation logic behind it.

    Providing information about the steps you've done so far and where the problems occurred might motivate the (mostly) volunteer helpers in this forum as well to avoid the impression that you simply drop off your tasks here for others to solve.

    Imke Feldmann - MVP Data Platform (PowerBI) - 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Saturday, June 22, 2019 4:24 AM
  • Hi Imke: Thanks for your advise. I have attached the file below. I want to achieve the outcome as per below in "Column K" in PQ. I then have to use this information to achieve what I posted in my first posting.

    Screen shot!Amc8fiGpDxekhXgh1uv_ZePfmb6Y

    Sunday, June 23, 2019 1:52 AM
  • Hi M.Awal. Sorry, but I have no idea how the screenshot above connects to your original screenshot, or how the "R"/"D"/etc. values are being computed. Can you clarify?


    Tuesday, June 25, 2019 7:41 PM
  • Hi Ehren: The first screen shot shows where inputs to be made and the second screen shot is where the Roster calendar to be generated from the input parameter made by the user. Sorry for the confusion. I was thinking may be it would be better if I dissect the whole problem and work my way through it. For example, first I want to create two additional columns based on the table below:

    1) highlight every 21st working days(only count Days & nights) an employee receives additional two rest days.

    2) Also as a custom column what would be the syntax to indicate every 2nd Tuesday or Thursday in the calendar. Thanks

        Source = List.Dates(#date(2019,07,01),366,#duration(1,0,0,0)),
        #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Added Index" = Table.AddIndexColumn(#"Converted to Table", "Index", 1, 1),
        #"Inserted Integer-Division" = Table.AddColumn(#"Added Index", "Integer-Division", each Number.IntegerDivide([Index], 8), Int64.Type),
        #"Inserted Modulo" = Table.AddColumn(#"Inserted Integer-Division", "Modulo", each Number.Mod([Index], 8), type number),
        #"Added Custom" = Table.AddColumn(#"Inserted Modulo", "Days", each if [Modulo]= 1 or [Modulo] = 2 then "Day"
    else if [Modulo]=3 or [Modulo]=4 then "Night"
    else "Rest Day", type text),
        #"Added Custom1" = Table.AddColumn(#"Added Custom", "Week Number", each [#"Integer-Division"]+1, type number)
        #"Added Custom1"


    Wednesday, June 26, 2019 10:45 AM
  • Hi M.Awal. Were you able to find a solution for this?


    Thursday, July 11, 2019 7:36 PM
  • Hi Ehren: Not yet. Please let me know if I am unclear about my request 1 and 2 as per above.
    Thursday, July 11, 2019 9:15 PM
  • Hi: Based on Column-E I would like to include 2 additional days off every 21 working day (only Day & night should be counted, not Rest Days). 



    Friday, July 12, 2019 12:01 PM
  • How are the "days off" supposed to be represented in the final output?


    Friday, July 12, 2019 6:06 PM
  • Hi Ehren: Thanks for your reply. Would it be possible to have the output represented as per follows dynamically? For example, if date range is for 1 year then the query calculates RDOs for 1 year or if the date range is for 4 years then it would calculate RDOs accordingly

    I have also attached a link of the output file.!Amc8fiGpDxekhX6sLCjK7VnnwZA0

    Friday, July 12, 2019 9:37 PM
  • Hi M.Awal. Were you able to find a solution to this?


    Tuesday, September 24, 2019 8:48 PM
  • Hi Ehren


    Wednesday, September 25, 2019 4:45 AM