#Value Error - Please Help

[mphopk]

I am trying to establish a start date by subtracting Column L and Col N from Column P. The result should be a date in column J. My data types are general. Please help, thank you.

Column J Date Entered into AutoNOA	Mgr Time DD:HH:MM:SS	Column L Auth Time DD:HH:MM:SS	RM Time DD:HH:MM:SS	Column N CPAC Time DD:HH:MM:SS	Request Number	Column P Date Assigned
#VALUE!	0:20:36:20	4:0:35:49	5:16:47:3	0:1:12:7	24JUN0HRAFRI0X136268	(P) 6/10/2024 2:22:42 AM

 

[Micron]

Pretty sure you cannot subtract time values from values formatted as dates. You might be able to achieve what you want if you convert time portions to numbers using Time() but you would have to ignore any Day portions and replace the colons in those values (e.g. 01:14:23 as hh:mm:ss) with commas. Then you could subtract your time values as serial numbers from a date if you convert the date to a number as well.
L, M and N are the numbers for the time portions you posted. The resulting date is in K. Not sure if that is what you expect as a result. If your post said time portions were important in the calculation result I missed that. You could have several expressions with different time values and still end up with the same date.

L: =TIME(20,36,20)
M: =TIME(0,35,49)
N: =TIME(16,47,3)
K: =Q1-L1-N1

Someone has likely produced a vba function for this, but not specifically for your column addresses. If not, I'd say it's doable if you can make use of code.

 

[Skyybot]

I did very little testing on this UDF. Please test on a copy of your Workbook. If you're not familiar with VBA. Hit Alt+F11. Right Click on your Workbook. Select insert Module. Paste this code inside of that module and use the Function like you would any other Excel Function. This UDF assumes that your data will always be in the displayed format of your picture.

Option Explicit
Public Function StartDate(ByVal End_Date As Range) As Date
Dim et, t1, t2, tt(3), r1 As Range, r2 As Range, i As Long
Set r1 = End_Date.Offset(0, -2): Set r2 = End_Date.Offset(0, -4)
et = Right(End_Date.Value, Len(End_Date.Value) - 3)
et = Trim(et)
et = CDate(et)
t1 = Split(r1.Value, ":"): t2 = Split(r2.Value, ":")
For i = 0 To UBound(t1)
    tt(i) = CInt(t1(i)) + CInt(t2(i))
Next i
StartDate = et - tt(0) - TimeSerial(tt(1), tt(2), tt(3))
End Function

Book1
	J	K	L	M	N	O	P
5031	Date Entered into AutoNOA	Mgr Time DD:HH:MM:SS	Auth Time DD:HH:MM:SS	RM Time DD:HH:MM:SS	CPAC Time DD:HH:MM:SS	Request Number	Date Assigned
5032	6/6/24 12:34 AM	0:20:36:20	4:0:35:49	5:16:47:3	0:1:12:7	24JUN0HRAFRI0X136268	(P) 6/10/2024 2:22:42 AM
Sheet1

Cell Formulas
Range	Formula
J5032	J5032	=StartDate(P5032)

 

[Alex Blakenburg]

Assuming your data is on row 3, does this work by any chance ?

=TRIM(TEXTAFTER(P3,")"))-(TEXTBEFORE(L3,":")+TEXTAFTER(L3,":"))-(TEXTBEFORE(M3,":")+TEXTAFTER(M3,":"))