Subtract times from Userform textboxes

[s7y]

Hello everybody,

In my userform I have textboxes containing dates and others containing times.

I need to find a way to subtract times considering that:

textbox_EDA = Estimated date of arrival
textbox_ETA = Estimated time of arrival

textbox_ADA = Actual date of arrival
textbox_ATA = Actual time of arrival

textbox_EDD = Estimated date of departure
textbox_ETD = Estimated time of departure

textbox_ADD = Actual departure date
textbox_ATD = Actual time of departure

(The reason why I have the dates and not only the times is because flights can arrive one day and depart the say after)

ATA - ETA (gives the arrival delay, if any)
ETD - ATD (gives the departure delay, if any)

ATD - ATA (gives the actual ground time)

of course all the times given must keep in consideration also the dates...

Attached please find a test sheet where in cells X2:AA2 I have the formulas I would like to have in vba

Test.xlsm
	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1	ARRIVAL ETA		ARRIVAL ATA		DEPARTURE ETD		DEPARTURE ATD			Mail Net Weight		DELAY IN	TOT DELAY	Ground Time	AGT				REM
2	03/05/22	09:00	3-May	10:30	04/05/22	08:30	04/05/22	09:15		0		01:30	00:45	-00:30	22:45				0:45
3
4
Flight Details

Cell Formulas
Range	Formula
V2	V2	=E2-F2
X2	X2	=TEXT(P2-N2,"hh:mm")
Y2	Y2	=TEXT(T2-R2,"hh:mm")
Z2	Z2	=TEXT(R2-N2,"hh:mm")
AA2	AA2	=IF(P2>T2,T2+(1-P2),T2-P2)
AE2	AE2	=Y2-AB2-AC2-AD2

Thanks in advance for any help

 

[Micron]

IMO your date ought to be in the format of 01/01/2020 04:30:00 PM
Then you should be able to easily calculate because values like that are really numbers: 43831.6875 - day on the left of the decimal, time on the right. The difference between 2 such numbers is the date/time span, formatted depending on what you want to see (days, hours, minutes or seconds). IMO this is definitely the way to go if using vba as you indicated as you should be able to use DateDiff function in vba. If you must present dates and times in separate columns then I'd have a column with values as noted (hide if you must) and use sheet functions to show the day and time separately, but based on the column with the proper values. You can relate those columns to your userform controls. If you must separate them on the form, you can do so but have hidden control(s) that concatenate the portions into a proper date/time value.

of course all the times given must keep in consideration also the dates...

That is exactly why I'm suggesting this. Granted, it can involve some complicated expressions to convert the results into minutes and hours but there are a gazillion posted examples out there. The problem is that the hour portion is returned as a portion of the day, so when you see .25 it is not 25 minutes after the hour, but is one quarter of a day. Same with the minutes portion IIRC.