• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

how to count items that don't go passed a certain time and date

L

Lizzy

New Member
Hi all,

I need a formula count only the age of a case but only up to a certain time.
EG: case comes in at 12th Sept 2022 6:01pm - this needs to be counted in today's count (13th September 2022) - also only weekdays are counted - not weekend.
today should only be today's date to 6:00pm
I've attached a copy of a spreadsheet example - I also want it to update automatically.
sorry I hope I'm making sense. I'm having trouble getting my head around it.
 

Attachments

  • sample data.xlsx
    14.5 KB · Views: 6
I think that NETWORKDAYS, combined with adding 6hs (1-close) to the received date might give the results you require.
Code: 
= NETWORKDAYS(received+(1-close), today)
80910
 

Attachments

  • sample data (1).xlsx
    14.4 KB · Views: 8
thanks for this - but now I'm getting a #SPILL! error when I transfer the formula to the relevant spreadsheet.
this is the formula I'm using - =NETWORKDAYS([date email received]+(1-H157),G157) - I can't seem to make 'close' (H157) & 'today' (G157) static
 

Attachments

  • spill error.PNG
    spill error.PNG
    5.9 KB · Views: 10
The formula gives the entire column of results by spilling the dynamic array from a single cell. If you are trying to return such an array within an Excel Table there will be some conflict giving rise to a #SPILL! error. The answer is to set the formula up to deal with a single entry and allow the Table to propagate the result down rather than the dynamic array.
Code: 
Using the structured reference
= NETWORKDAYS([@[date email received]]+(1-close), today)

Using a defined name
= NETWORKDAYS(@received+(1-close), today)
At the moment 'today' is a defined name that references a cell containing the formula '= TODAY()' so it is volatile and changes daily. Overwriting the formula with a static value will make the reference static. The defined name 'close' is static and is simply the end of your working day i.e. 18:00:00.
 

Attachments

  • sample data (2).xlsx
    16.6 KB · Views: 4
I appreciate you trying to help - but it is just not working - I'm assuming I have to define 'today' and 'close' somehow? I've made a mess of it now I'm getting #NAME? error. seriously, this is doing my head in :-(
 
here's a snippet - I have to be careful providing the actual sheet as it has sensitive data
 

Attachments

  • name error.PNG
    name error.PNG
    25.4 KB · Views: 4
I've figured out how to define a name. However the formula is not returning the correct result.
EG - on the sample SS
'date email received' = 13/09/2022 9:10 am is showing 'aged' = 3, however today's date right now is 15/09/2022 9:27 - this means the result should be 2 days old. right?
 
My apologies for causing you confusion with the defined names. I haven't used a direct cell reference of the type $A$1 since 2015 so names come naturally to me.

As for the main problem, you are correct. NETWORKDAYS returns inclusive time periods, so 15/09/2022 3:17 to 15/09/2022 9:27 would return 1 working day. The answer would appear be to subtract 1 from the count.

Code: 
= NETWORKDAYS([@[date email received]]+(1-close),today) - 1
 
You must log in or register to reply here.
Back
Top