Geek Girl 007
Board Regular
- Joined
- Mar 12, 2022
- Messages
- 152
- Office Version
- 2021
- Platform
- Windows
I want O2 to go red if O2 is more than 5 working days.
This is the formula I used, but it didn't work
=NETWORKDAYS.INTL(TODAY(),O2,1)<=5
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Format if date is more than 5 working days
- Thread starter Geek Girl 007
- Start date
I want O2 to go red if O2 is more than 5 working days.
This is the formula I used, but it didn't work
=NETWORKDAYS.INTL(TODAY(),O2,1)<=5
Excel Facts
Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
hagia_sofia
Well-known Member
- Joined
- May 10, 2024
- Messages
- 610
- Office Version
- 365
- Platform
- Windows
Hello, would this work for you:
Excel Formula:
=NETWORKDAYS.INTL(O2,TODAY(),1)>=5
Upvote
0
Geek Girl 007
Board Regular
- Joined
- Mar 12, 2022
- Messages
- 152
- Office Version
- 2021
- Platform
- Windows
Sorry, I just tried it with different dates, and it is only working at 5 days, not working days???
Upvote
0
PeteWright
Active Member
- Joined
- Dec 20, 2020
- Messages
- 492
- Office Version
- 365
- Platform
- Windows
- Mobile
bump... already solved
Upvote
0
MARK858
MrExcel MVP
- Joined
- Nov 12, 2010
- Messages
- 16,732
- Office Version
- 365
- Platform
- Windows
- Mobile
Works for me (shouldn't have the equals sign though if greater than 5 working days)
| Book1 | ||||
|---|---|---|---|---|
| O | P | |||
| 2 | 17/08/2024 | Saturday | ||
| 3 | 05/08/2024 | Monday | ||
| 4 | 06/08/2024 | Tuesday | ||
| 5 | 07/08/2024 | Wednesday | ||
| 6 | 08/08/2024 | Thursday | ||
| 7 | 09/08/2024 | Friday | ||
| 8 | 10/08/2024 | Saturday | ||
| 9 | 11/08/2024 | Sunday | ||
| 10 | 12/08/2024 | Monday | ||
| 11 | 13/08/2024 | Tuesday | ||
| 12 | 14/08/2024 | Wednesday | ||
| 13 | 15/08/2024 | Thursday | ||
| 14 | 16/08/2024 | Friday | ||
Sheet1 | ||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| P2:P14 | P2 | =O2 |
| O4:O14 | O4 | =O3+1 |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| O2:O14 | Expression | =NETWORKDAYS.INTL(O2,TODAY(),1)>5 | text | NO |
Upvote
0
Geek Girl 007
Board Regular
- Joined
- Mar 12, 2022
- Messages
- 152
- Office Version
- 2021
- Platform
- Windows
Maybe because it's a Sunday and I shouldn't be working, but I can't work out what I'm missing?
So sorry
Upvote
0
MARK858
MrExcel MVP
- Joined
- Nov 12, 2010
- Messages
- 16,732
- Office Version
- 365
- Platform
- Windows
- Mobile
Don't know where you are getting the 8 from
| Book1 | ||||
|---|---|---|---|---|
| O | P | |||
| 2 | 17/08/2024 | 0 | ||
| 3 | 05/08/2024 | 10 | ||
| 4 | 06/08/2024 | 9 | ||
| 5 | 07/08/2024 | 8 | ||
| 6 | 08/08/2024 | 7 | ||
| 7 | 09/08/2024 | 6 | ||
| 8 | 10/08/2024 | 5 | ||
| 9 | 11/08/2024 | 5 | ||
| 10 | 12/08/2024 | 5 | ||
| 11 | 13/08/2024 | 4 | ||
| 12 | 14/08/2024 | 3 | ||
| 13 | 15/08/2024 | 2 | ||
| 14 | 16/08/2024 | 1 | ||
Sheet1 | ||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| P2:P14 | P2 | =NETWORKDAYS.INTL(O2,TODAY(),1) |
| O4:O14 | O4 | =O3+1 |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| P2:P14 | Cell Value | >5 | text | NO |
| O2:O14 | Expression | =NETWORKDAYS.INTL(O2,TODAY(),1)>5 | text | NO |
Upvote
0
Solution
Geek Girl 007
Board Regular
- Joined
- Mar 12, 2022
- Messages
- 152
- Office Version
- 2021
- Platform
- Windows
The 8 is a different format, that's the total number of days: =TODAY()-O2
I want the date formula to show up in column O
Upvote
0
PeteWright
Active Member
- Joined
- Dec 20, 2020
- Messages
- 492
- Office Version
- 365
- Platform
- Windows
- Mobile
If I got you right, you are looking for a formula with 6 working days (monday to saturday) and only sunday as weekend and he formula should count if there are less then 5 working days.
Is this working for you?
Is this working for you?
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B2:B3 | B2 | = NETWORKDAYS.INTL(A2,TODAY(),11) |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| A2:A3 | Expression | = NETWORKDAYS.INTL(A2,TODAY(),11) <= 5 | text | NO |
Upvote
0
Similar threads
- Locked
- Question
- Question
- Question
