0

Count missing dates

Hey all! I'm back with ANOTHER question.

Is it possible to use Ninox to count missing dates? For example: on a timesheet I may work August 1,2,3,4,5,7,8,10,11,12,13,14,19,20,22,23,24,25,26,27,30 and 31. This will be done for an entire year.

Can I get Ninox to scan through the Timesheet table and give me a number of days NOT entered? So in this example 9? The days off may vary so can't go by day or date? I just need a number returned?

 

Thanks again for your help.

4 replies

null
    • Daniel_Berrow
    • 11 mths ago
    • Reported - view

    If you have a calendar table with each day in it, you could then link these timesheets to the day of the calendar and count how many records in that month/year do not have a linked timesheet against it for that individual

      • John_Wilmans
      • 11 mths ago
      • Reported - view

        I have a table with date, time in and time out. It has dates which is a calendar box and times as per Ninox date and time boxes. Could I work it with that?  Or should I create a “calendar” table and copy dates across to that then count?

    • Daniel_Berrow
    • 11 mths ago
    • Reported - view

    You can do it with the fields you have already, though it would be more difficult. You can count the records you have for that month, and then have a formula looking at the first of selected month -> first of the next month and counting the days between the 2, then take the two away from each other

    let t := this;
    let countOfWorkdays := cnt(select timesheets[month(date) = t.month(date)][year(date) = t.year(date)]);
    let monthLength := days(date(year(date), month(date), 1), date(year(date), month(date+1), 1));
    let countOfOtherDays := monthLength - countOfWorkdays;
    countOfOtherDays
    

    Something along these lines should work for you. It will work dynamically through the months, or you can add some fields in to select which month/year you want to see, depends how you want the output to be

      • John_Wilmans
      • 11 mths ago
      • Reported - view

      Thanks man. That's the ticket!

Content aside

  • Status Answered
  • 11 mths agoLast active
  • 4Replies
  • 48Views
  • 2 Following