Userform text values - Adding minutes to a time

[RobbieC]

Hi there, I have a userform which displays a time in a textbox (StartTime), for example 11:15

I have a second input box which the user types in the number of minutes a task has taken (TimeTaken) - this is always in minutes (eg 6hrs = 360)

And there is a third textbox (EndTime) which is calculated from the StartTime + TimeTaken...

My question is "how do I add the two together to get the EndTime?"

If the StartTime is 11:15 and the TimeTaken is 185 minutes the EndTime should be 14:20

But what happens if the StartTime is 22:45 and the TimeTaken is 360minutes? The EndTime is 04:45...

But how? Is it a case of converting everything to minutes and then converting back to hours at the end? I dunno the best way to go about this...

If you can give me a nudge in the right direction, I'd be very grateful

Thanks

 

[pkdeimos]

You can try the below formula -
If A1 = 22:45, B1=360
Result A2 = A1+TIME(0,360,0)

It gives the required result 04:45. Hope this helps.

 

[RobbieC]

This works in cells, however not in userform vba

I think I need to declare the variables as date/time and then do the math, and then convert back to hours&mins

I can't figure it out...

 

[RobbieC]

Ok,so I'm getting a bit closer

EndTime.Value = DateAdd("n", TimeTaken.Value, StartTime.Value)

This gives me the correct EndTime...

But if the EndTime goes over into the next day, it gives

31/12/1899 05:45:00

How can I format the EndTime result so that it is just the time andNOT the date as well?

Thanks

 

[RobbieC]

Ok, so I found a way to botch the endtime (if anyone else was wondering...)

EndTimeVar = DateAdd("n", [COLOR=#333333]TimeTaken.Value[/COLOR], StartTime.Value)


EndTimeVar = Format(EndTimeVar, "hh:nn")


EndTime.Value = Right(EndTimeVar, 8)

This now gives me just the time at the end of the date string... it does the trick