Calculate No of Working days between two specific days excluding weekends

[Aswinraj]

Hi Friends,

Hello Friends

I need a help to achieve some formula..,

When we input the date in "A1", it has to calculate No of Working days from given date till TODAY and if the No. of Working days is below 1 to 10 then it should shows "Aging 1 to 10",
and if No. of Working days is between 11 to 20 then it should shows "Aging 11 to 20" and if No. of Working days is between" >20" then it should shows as "Aging more than 20"

Kindly help..,

Assume Today's date is 06/21/2018, So if input is 06/01/2018 then the difference is "13 Working days" (excluding Saturday/Sunday) so the Output will be "Aging 11 to 20"
[TABLE="class: text_table, width: 500"]
<tbody style="margin: 0px; padding: 0px; border: 0px; font-family: inherit; vertical-align: baseline;">[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[/TR]
[TR]
[TD]06/01/2018[/TD]
[TD]Aging 11 to 20[/TD]
[/TR]
[TR]
[TD]06/18/2018[/TD]
[TD]Aging 1 to 10[/TD]
[/TR]
</tbody>[/TABLE]


With Regards,
Aswinraj A

 

[Marcelo Branco]

Maybe...

B1 copied down
=LOOKUP(NETWORKDAYS(A1,TODAY()),{1;11;21},{"Aging 1 to 10";"Aging 11 to 20";"Aging more than 20"})

M.

 

[Aswinraj]

@Marcelo Branco Thanks a lot it works.., but can you explain what is {1;11;21} ? how it works.., wanted to understand the concept of this formula.

 

[Logit]

.
Another method :

[TABLE="width: 120"]
<colgroup><col></colgroup><tbody>[TR]
[TD][TABLE="width: 1029"]
<colgroup><col><col><col><col><col span="2"><col><col span="5"></colgroup><tbody>[TR]
[TD="colspan: 4"]Formula B2 and drag down the colum : =NETWORKDAYS(A2,NOW())[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 11"]Formula C2 and drag down the column : =IF(AND(B2>0,B2<11),"Aging 1 to 10","")&IF(AND(B2>10,B2<21),"Aging 11 to 20","")&IF(B2>20,"Aging more than 20","")


Download example workbook : https://www.amazon.com/clouddrive/share/jJspAO5eWcveD1c89lRpuAuB621QJl2GA8wbXudcYce[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

@Marcelo Branco Thanks a lot it works.., but can you explain what is {1;11;21} ? how it works.., wanted to understand the concept of this formula.

Syntax of LOOKUP function: LOOKUP(lookup_value, lookup_vector, [result_vector])
The function requires that the values in lookup_vector be placed in ascending order

In your case:
The values in the lookup_vector {1;11;21} correspond to the lowest value of each track:
Track1: 1 to10
Track2: 11 to 20
Track3 (greater than 20): 21

The values in the result_vector are the desired results correspondent to each track.

The numbers in the range 1 to 10 fall in the first track and the formula returns the correspondent value (the first) in the result_vector --> Aging 1 to 10;
the numbers in the range 11 to 20 fall in the second track and the formula returns the correspondent value (the second) in the result_vector --> Aging 11 to 20;
the numbers greater than 20 (21 or more) fall in the third track and the formula returns --> Aging more than 20

Take a look at
https://support.office.com/en-us/article/lookup-function-446d94af-663b-451d-8251-369d5e3864cb

M.

 

[Aswinraj]

@Marcelo Branco Thanks a lot for your time to help and explanation


With Regards,
Aswinraj A