Get networkdays to work on a concatenate date and time entry

[rugby_nut]

Hi all,

I have a sheet where a user would manually input a date in one cell and a time in another adjacent cell. I have used concatenate to pull these together in another cell. Using networkdays I cannot get the formula to calculate the difference between the date/times and return day(s), hour(s) and min(s).
If I manually type the date and time, the networkdays function works, it's only when trying to use the concatenate formula it all goes sideways.
Any help would be great.
Additionally, It would be great to learn of any quicker ways for a user to input a date and time to maintain a standardised input.

I've attached the sheet I'm working on. Thank you...

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1					Manual entry					Manual entry	=NETWORKDAYS(E4,J4)+MOD(J4-E4,1)-1	=INT(K4)& " days "& HOUR(K4) &" hrs "& MINUTE(K4) &" mins"
2					Tue 17/12/2019 10:14					Sun 22/12/2019 14:32	3.179166667	3 days 4 hrs 18 mins
3					Mon 09/12/2019 10:14					Mon 09/12/2019 15:14	0.208333333	0 days 5 hrs 0 mins		Longest	Shortest
4					Mon 09/12/2019 18:14					Tue 10/12/2019 15:14	Sun 01/01/1900 21:00	1 days 21 hrs 0 mins		3 days, 4 hours	0 days, 5 hours
5
6	User input	User input	=CONCATENATE(TEXT(A2,"ddd dd/mm/yy")&" "&TEXT(B2,"hh:mm"))			User input	User input	=CONCATENATE(TEXT(F1,"ddd dd/mm/yy")&" "&TEXT(G1,"hh:mm"))			=NETWORKDAYS(C6,H6)+MOD(H6-C6,1)-1	=INT(K6)& " days "& HOUR(K6) &" hrs "& MINUTE(K6) &" mins"
7	Tue 17/12/2019	10:14	Tue 17/12/19 10:14			Sun 22/12/2019	14:32	Sun 22/12/19 14:32			#VALUE!	#VALUE!
8	Mon 09/12/2019	10:14	Mon 09/12/19 10:14			Mon 09/12/2019	15:14	Mon 09/12/19 15:14			#VALUE!	#VALUE!
9	Mon 09/12/2019	18:14	Mon 09/12/19 18:14			Tue 10/12/2019	15:14	Tue 10/12/19 15:14			#VALUE!	#VALUE!
Sheet1

Cell Formulas
Range	Formula
K2:K4	K2	=NETWORKDAYS(E2,J2)+MOD(J2-E2,1)-1
L2:L4,L7:L9	L2	=INT(K2)& " days "& HOUR(K2) &" hrs "& MINUTE(K2) &" mins"
N4	N4	=MAX(K2:K4)
O4	O4	=MIN(K2:K4)
K7:K9	K7	=NETWORKDAYS(C7,H7)+MOD(H7-C7,1)-1
C7:C9,H7:H9	C7	=CONCATENATE(TEXT(A7,"ddd dd/mm/yy")&" "&TEXT(B7,"hh:mm"))

 

[Fluff]

Why not just add the 2cells together & then use cell formatting to change how it's displayed.

=A7+B7

 

[rugby_nut]

Thank you Fluff, I've worked that out now.

I do have another issue though, when using the network days function.

I have the end date one day more than the start date, but the end time is less than the start time, the result still shows over 1 day.

So the start date is Tue 14/06/22 10:00 and the end date is Wed 15/06/22 08:30 the result shows 01 days 22hrs 30 mins, whereas it should show 00 days 22hrs 30mins

Can I get some advise with this please

 

[bebo021999]

Con1.xlsm
	A	B	C
1	14/06/2022 10:00	15/06/2022 08:30	22:30:00
Sheet2

Cell Formulas
Range	Formula
C1	C1	=NETWORKDAYS(A1,B1)-1+MOD(B1,1)-MOD(A1,1)

 

[rugby_nut]

Thank you bebo021999!! That solved the issue