Production Days Late/Early

[Offroadracer_814]

Assistance needed please. I am working of a formula that is not really working for my needs. I am trying to find out how many days late or early our production equipment is.

Column A is the days late or early. (I have days late formatted to bold red text and highlighted.)
Column B is our planned completion date.
Column C is reality. Or actual production date.

In Row 2 is the formula I use. Is there a better formula? I am not confident in it.

Thanks

 

[zero269]

Hi @Offroadracer_814 ,

I'd recommend using the forums Excel Add-in to make it easier to share your sample data for others to help you.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

However, I think the following will help you achieve your desired results:

• Difference v1 is using the basic NETWORKDAYS formula. However, it's including the Planned date in the total.
• Difference v2 is using the Holiday portion of the formula to exclude the Planned date from the total.
• Difference v3 is just using v3 and joining the total with some string values for: Early and Late.

I would avoid v3 as sorting/filtering in place may be problematic. Plus, if you are using those values in a report, it will be harder to extract or FILTER from.

I think v2 & Status would be the most helpful. The Status column will help make it easier to Sort/Filter instead of a Number Range in the Difference column.

VBA Testing.xlsm
	A	B	C	D	E	F
1	Planned	Completed	Difference v1	Difference v2	Status	Difference v3
2	2024-08-02	2024-07-26	6	5	Late	Late -5
3	2024-07-24	2024-07-27	3	2	Early	Early 2
4	2024-07-27	2024-07-27	0	0	Ontime	Ontime
NETWORKDAYS

Cell Formulas
Range	Formula
C2:C4	C2	=NETWORKDAYS([@Planned],[@Completed])
D2:D4	D2	=NETWORKDAYS([@Planned],[@Completed],[Planned])
E2:E4	E2	=LET( n, NETWORKDAYS([@Planned],[@Completed],[Planned]), IF(n=0,"Ontime",IF(n<0,"Late","Early")))
F2:F4	F2	=LET( n, NETWORKDAYS([@Planned],[@Completed],[Planned]), IF(n=0,"Ontime",IF(n<0,"Late " & n,"Early " & n)))

To display negative numbers in red, just format the Column with Number Formatting, 0 decimals and select the second one in the list.

 

[zero269]

To display negative numbers in red, just format the Column with Number Formatting, 0 decimals and select the second one in the list.

Or just stick with -1234 so reports and printing isn't a problem. If you still want red negative numbers, then I presume you're currently using Conditional Formatting: Highlight Cells Less Than 0