Counting workdays to the present day

[Sambrowne]

Probably been asked a million times but really struggling to find the right answer. I need to count the workdays since a specific date but need it to update on the day it opens. For example in A1 I type the activity as started on 1 Dec 20 and so in A2 it would say 8 (working days) when I open it today. When I open the doc on Monday it would say 9, Tues 10 and so on.

Column A1 is where the dates would sit but some may be blank until a date is entered so the formula would need to be able to cope with an empty field.

Hope I'm making sense, can always screenshot the spreadsheet if that helps.

 

[Fluff]

How about

=IF(A1="","",NETWORKDAYS.INTL(A1,TODAY())-1)

 

[Sambrowne]

How about

=IF(A1="","",NETWORKDAYS.INTL(A1,TODAY())-1)

Wow, that was quick and worked a charm - thanks! Can I be cheeky and ask another question? I need to colour code the cells at the same time so less than or equal to 15 should be green, 16-19 should be amber and 20+ would be red. Any ideas?

 

[Fluff]

You can use conditional formatting for that & use

=AND(A2<>"",A2<=15)
=AND(A2<>"",A2<=19)
=A2>=20

 

[Sambrowne]

I'll give it a go, thanks again!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Sambrowne]

Somewhat typically I have another question based on what went before but a bit more complex. Not sure if I'm straying into Pivot Table territory though. In my table I have Col L that has an initial date and in N I have an end date. In O I want to calculate the number of workdays between L and N. So far so easy but here's where it gets complicated.

L feeds N so if L has a date in it but N doesn't, I want the the cell in O to count the workdays since the date in L until a date is entered at which point in it counts the workdays between the two. For example, if the date in L is 1 Dec but no end date in N and I open the doc on 5th Dec, O should say 4, if I open it on 9th Dec it should say 7, open it on 10th it should say 8 and so on.

However, as soon as I enter a date in N, let's say 20 Dec, then O should calculate the workdays between 1st and 20th (14 workdays) and stop counting. I'd also like to colour code it as before. Is any of that possible? I tried to post a screenshot of my table but apparently it's too big, do you know the size that will fit?

Thanks for any help you can offer.

 

[Sambrowne]

Link:

https://flic.kr/p/2kfSAKD

 

[Dave Patton]

T202012a.xlsm
	L	M	N	O
1	Today is		Fri 11-Dec-20
2				Workdays
3	Mon 09-Nov-20		Mon 30-Nov-20	16
4	Tue 01-Dec-20			9
5	Tue 01-Dec-20		Sat 05-Dec-20	4
6	Tue 01-Dec-20		Wed 09-Dec-20	7
7	Fri 04-Dec-20		Mon 07-Dec-20	2
8	Fri 04-Dec-20		Fri 11-Dec-20	6
9	Fri 04-Dec-20			6
10	Fri 04-Dec-20		Fri 11-Dec-20	6
8b

Cell Formulas
Range	Formula
O3:O8	O3	=NETWORKDAYS(L3,IF(N3="",TODAY(),N3))
O9:O10	O9	=NETWORKDAYS.INTL(L9,IF(N9="",TODAY(),N9),1)

Edit depending on your definition of Workdays. Mon - Fri?
Consider Holidays. List?
Review Excel's Help for Networkdays and Networkdays.intl

​

 

[Sambrowne]

Thanks very much for this, really helpful. I have another question though, can the formula take into account an empty cell in L? In my screenshot above L11, 17 and 20 are empty, awaiting dates but with your formula it currently displays 31555. Not sure what to do next, any ideas?