# of assessments running a day based on Start Time and Stop Time

[okiedokie2017]

Hello, 

I want to look at how many assessments running per day.

In the spreadsheet, there are "StartTime" and "StopTime" columns. These show the date and the time an assessment starts running and stops running. An assessment can either starts and stops in one day, or sometimes it can go on for several days or up to several weeks. 

So the task is a bit tricky as Im not looking at number of unique assessments per days, Im jsut purely looking at how many assessments per day in a specific month. 

Is there a way I can solve this? 

Many thanks 

[Ashish_Mathur]

Hi,

Share a dataset and show the expected result.

[okiedokie2017]

Example: 

Start Time:                                StopTime                              Expected Result

20 October 2017 3 am             20 October 2017   5am         1 day

10 September 2017  6 am       12 September 2017  7pm      2 days 

18 October 2017 3pm              2 November 2017 11pm       15 days 

1 November 2017                    "Blank"                                    N/A

If an assessment has a "blank" stoptime, that means it is still running. I would like to create a column showing me how may days each assessment ran. 

But my main goal is actually to create a graph showing the exact number of assessment running per day in a month. If an assessment runs for 18 days, it will be counted for all18 days, etc. 

[Ashish_Mathur]

Hi,

For the second row, the answer should be 3 days - am i correct?  Also, what is the purpose of the time stap in each cell?  Does that time stamp play an role in determinig the numer of days?

[okiedokie2017]

3 days is correct. My apologies. 

Let just ignore the timestamp for now, since it wasnt what I was looking for. I believe the date is good enough to achieve what I was hoping to. 

Best, 

[Ashish_Mathur]

Hi @okiedokie2017,

You may download my solution from here.

Hope this helps.

[okiedokie2017]

Hello @Ashish_Mathur,

Thank you for sending the file. Much appreciated. 

I was trying to recreate the same thing, however, in "Edit Query", when I was trying to add the Custom Column with the formula: 

if [Stop Time]=null then DateTime.Date(DateTime.LocalNow()) else [Stop Time]

It doesnt seem to work, especially when I replaced "null" with "Blank()". As my datasets have blanks in there. any thoughts why this happened? 

 Many thanks! 

[Ashish_Mathur]

You are welcome.  What happens when you replace null with ""