Userform input start/stop time and duration

Hi all

Except YouTube tutorials I am complete new to VBA and this is my first thread ever, so be nice to me :-) . I have a course coming up in a month but I need to finish this ASAP, so I hope you guys will help me. I am making a time calculation project and I'm almost done, but I have a few hurdles that I can't pass:

1) The time input is not showed correctly.

- In Denmark we use 00:00-2359 and no AM or PM. To get the user to type only this kind of input I made some If-rules so the user can't type it differently. But I don't the user to type in the colon him self (e.g. from 1345 to 13:45). So basically I am asking how to do this a simple way?

I have tried formatting the cells in Excel with no luck. I have also tried this code:

Range("a1").Value = Left(Range("a1").Value, 2) & ":" & Right(Range("a1").Value, 2)

The code works almost fine, but not if the time is before 10:00 (between 00:00 and 09:59). What can I do?

2) Calculation across midnight
- The other problem is when start time is before 23:59 and the stop time after 00:00.

This is my code: Code.txt

I have been trying for 5 hours and I haven't got any further. Can you help me please?


Andy

Thank you so much - I am so grateful!

No. 1 works perfectly but no. 2 does still not work across midnight.

Do I need to do something special to make no. 2 work? Like a special formatting? Maybe I should mention that I have no Dim in my project.

My code from you look like this now:

Please Login or Register  to view this content.

When I use e.g. 23:00 to 01:30 I get -1,89583333333333 as result of duration.

Am I using your code no. 2 wrong?

Thanks again!

Andy

Flappy_Owl_V1.0.xlsm

Amazing! Thanks! I have attached a VBA-game as a present.

Let me know if I can return th favor one day (I'm good with network :-)).

How do I mark the thread as SOLVED?

I'm sorry - I got too exited about your code. It is working but not for my purpose.

Right night the code works like this:

Start: 12:00
End: 12:30
Duration: 0,30

What I need is this:

Start: 12:00
End: 12:30
Duration: 0,50

Can you help me again, protonLeah?

It's not working.

Start: 17:00
End: 17:30
Duration: 16,79

What can I do?

A formula like: =(B1-A1)*24 will not work if start time is before midnight and end time is after midnight. That was why my problem started.

Here is a part of the code:

Please Login or Register  to view this content.

I attach my full userform code too. Userform.txt

You see?

It's still not working - I'm so confused!

Is your offset right?

ActiveCell.Offset(0, 0) is the date
ActiveCell.Offset(0, 1) is the start time
ActiveCell.Offset(0, 2) is the end time
ActiveCell.Offset(0, 3) is the duration

Finally I think I'm there.

Thank you all!!

How do I mark the thread as solved?