Subtract times from Userform textboxes

s7y

New Member
Joined
Jun 25, 2010
Messages
13
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
MNOPQRSTUVWXYZAAABACADAEAF
1ARRIVAL ETAARRIVAL ATADEPARTURE ETDDEPARTURE ATDMail Net WeightDELAY INTOT DELAYGround TimeAGTREM
203/05/2209:003-May10:3004/05/2208:3004/05/2209:15001:3000:45-00:3022:450:45
3
4
Flight Details
Cell Formulas
RangeFormula
V2V2=E2-F2
X2X2=TEXT(P2-N2,"hh:mm")
Y2Y2=TEXT(T2-R2,"hh:mm")
Z2Z2=TEXT(R2-N2,"hh:mm")
AA2AA2=IF(P2>T2,T2+(1-P2),T2-P2)
AE2AE2=Y2-AB2-AC2-AD2



Thanks in advance for any help
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,034
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top