3 Replies Latest reply on Jul 8, 2016 8:28 AM by Shinichiro Murakami

    formula required the total count of weekday in a month

    Anh Ho

      Hi all,

       

      I have this data (please see excel file attached which I need to calculate occupancy for each car park. The formula is:

      Occupancy for car park A (%) = total turnaround time for all cars in carpark A / total count of weekdays in a month

                                                     

      I don't know which formula to use to calculate total count of weekdays in a month that make this calculation valid. Please help

      Thank you

        • 1. Re: formula required the total count of weekday in a month
          Shinichiro Murakami

          I cannot understand how to read your data, so I just created simple date calendar to explain date counting.

           

          [YYYYMM]   //  specify the year and the month from date

          year([Date])*100+month([Date])

           

          [weekday]  // get weekday

          datepart('weekday',[Date])

           

          Below formula only works if your data has whole set of days

           

          [Count weekday in month]  // count days in month excluding 1(Sunday) and 7(Saturday)

          {fixed[YYYYMM] :countd(if ([weekday]<>1 and [weekday]<>7) then [Date] end)}

           

          In case of which your data misses a part of days

           

          [Last Sunday]

          {fixed [YYYYMM]:max(if [weekday]=1 then [Date] end)}

           

          [Last Saturday]

          {fixed [YYYYMM]:max(if [weekday]=7 then [Date] end)}

           

          [weekends]

          int((day([Last Sunday])-1)/7)+1

          +

          int((day([Last Saturday])-1)/7)+1

           

          [Day in Month]

          day(datetrunc('month',dateadd('month',1,[Date]))-1)

           

          [Calc weekdays in month]

          [Day in Month]-[weekends]

           

          Thanks,

          Shin

           

          9.1 attached

          • 2. Re: formula required the total count of weekday in a month
            Anh Ho

            Shinichiro,

             

            Thank you for replying. Let me explain the data set a bit further for you.

            My goal is to see how busy each of the car park is during weekday by comparing the total turnaround time (minutes) for all cars parked in a given carpark in a month against total weekday minutes in that given month. For example,the total occupied minutes for car park no 1 (TB1) during weekday is 17,630 minutes, total available weekdays minutes in January is 31 days x 24 hrs x 60 minutes =44640 minutes.Then I can calculate:

            Carpark no.1 (TB1) occupancy (%) = 17630/44640*100=39.49%

             

            I want to be able to calculate and show that 39.49% in a chart.

            I hope it make sense -.-

            • 3. Re: formula required the total count of weekday in a month
              Shinichiro Murakami

              Oh, I took it too difficult.  Weekday does not mean Monday - Friday.,

               

              Anyways, I could not replicate your number exactly, but I hope you still can understand the concept of solutions.

               

              [Days in Month]

              day(datetrunc('month',dateadd('month',1,[Date]))-1)

               

              [Days in Month July]

              min(day({fixed [Year],[Month]:max([Date])}),[Days in Month])

               

              [Minutes in Month]

              24*60*[Days in Month July]

               

              [Utilization]

              sum([Minutes])/attr([Minutes in Month])

               

              Thanks,

              Shin