Format textbox as a duration

**chrisjames25** · 10-01-2013, 02:26 AM

Hi. I have a userform for data input. One of fields required is amount of time it took a worker to complete a job. How can I format the textbox so that for example if I typed in 0130 it would format it as 1hr 30mins.

No task will take longer than 24 hours.

Cheers for your help

**OllieB** · 10-01-2013, 02:48 AM

Hi Chris,

By definition textboxes are of a type variant. The user can input anything he/she wants. You can use the AfterUpdate event to format the data entered as shown below

Please Login or Register  to view this content.

quick and dirty, no validations

**chrisjames25** · 10-01-2013, 03:46 AM

Ollie as ever many thanks for your quick and correct response.

**chrisjames25** · 10-01-2013, 03:52 AM

Ollie

If I then had a text box with no. of people, what would I need to do to multiple number of people.value by duration. this is what I have but it fails:

Please Login or Register  to view this content.

**OllieB** · 10-01-2013, 04:58 AM

The format instruction is just taking the value in the textbox and inserts a ':' in between for DISPLAY purposes only. It does not translate the value entered into hours and minutes so using the value as a source for your calculations of the field total hours is not going to work.

you could try

Please Login or Register  to view this content.

Note that the above 'trick' does not work if the total number of hours exceeds 24

**chrisjames25** · 10-01-2013, 05:28 AM

Hi Ollie. WIll test the above shortly. Sure it works though.

Any chance there is a solution is the work exceeds 24 hours. sometimes we have 12 people on a job so whilst job may take only 4 hours it is easy to exceed total hours of 24.

**chrisjames25** · 10-01-2013, 05:31 AM

HI Ollie

Just tested it using the following

Please Login or Register  to view this content.

Getting nothing in total hours other than " : "

Any thoughts?

**OllieB** · 10-01-2013, 05:37 AM

Chris,

created a form with the three textboxes, entered 3 in the people box and 0130 in the hours box. The value 0130 gets formatted as 01:30 and the total box shows 04:30. What values did you enter?

**OllieB** · 10-01-2013, 06:58 AM

Hi Chris,

I missed your post about > 24 hours. I have re-written the code. The input time may now be specified as 0130, 130 or 1:30 and the total can be higher than 24 hours

Please Login or Register  to view this content.

**chrisjames25** · 10-01-2013, 08:24 AM

THis is working great but with the none validation issues with the hourstxt I can see errors occurring so was wondering whether a solution would be to have 2 text boxes. One for the hours and one for the minutes and somehow combine them in a third textbox in the format of a duration and apply the third text box to the above scenario. Thoughts as ever appreciated?

**OllieB** · 10-01-2013, 08:30 AM

Hi Chris,

not sure what you are saying. Are you still getting errors?

I tried many combinations of input but am unable to replicate.

Can you give me some examples?

**chrisjames25** · 10-01-2013, 08:44 AM

apologies for not being clear.

THe following code you provided works perfectly

Please Login or Register  to view this content.

WHat I am worried about now is the formatting code which allows you to enter duration in the hourstxt could lead to errors as has no validation. for example if I saw 1 hour on the timesheet and quickly typed in 1 in hourstxt I get 1 minute as opposed to 1hour and 0 minutes. SO I am wondering whether I replace the hourstxt with two text boxes labelled hours and minutes and then combine there values to form a duration in a third textbox.

Hope that makes more sense.

**OllieB** · 10-01-2013, 08:50 AM

it does :-)

having two different input boxes for hours and minutes certainly allows for more reliable data entry and validation. Adjusting the code to deal with this is quite easy:

Use the AfterUpdate event for the separate textboxes to check whether the value entered is correct (actually only on the minutes textbox which cannot exceed 59), and make the following adjustments to the code

replace

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

and, replace

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

and, finally remove

Please Login or Register  to view this content.

**chrisjames25** · 10-01-2013, 08:53 AM

Sorted that bit thankyou. Sorry I knew how to do that I was just being stupid.

Ok final problem I promise (at least I think it is the final problem) - I have 2 systems I have to enter these hour figures into. one works in hours and minutes and the other works in decimal time. SO how to I turn 2hours and 40 minutes into 2.66 for a final text box

**chrisjames25** · 10-01-2013, 08:54 AM

ahhh we crossed over in cyber space. MAny thanks for your suggestion - as ever it is far more elegant than mine.

**OllieB** · 10-01-2013, 09:04 AM

Originally Posted by chrisjames25

Sorted that bit thankyou. Sorry I knew how to do that I was just being stupid.

Ok final problem I promise (at least I think it is the final problem) - I have 2 systems I have to enter these hour figures into. one works in hours and minutes and the other works in decimal time. SO how to I turn 2hours and 40 minutes into 2.66 for a final text box

Please Login or Register  to view this content.

**chrisjames25** · 10-01-2013, 04:41 PM

As ever annoying I can't add more rep for your assistance. Promise will do when add to someone else

Format textbox as a duration

LinkBack

Thread Tools

Rate This Thread

Display

Format textbox as a duration

Re: Format textbox as a duration

Re: Format textbox as a duration

Re: Format textbox as a duration

Re: Format textbox as a duration

Re: Format textbox as a duration

Re: Format textbox as a duration

Re: Format textbox as a duration

Re: Format textbox as a duration

Re: Format textbox as a duration

Re: Format textbox as a duration

Re: Format textbox as a duration

Re: Format textbox as a duration

Re: Format textbox as a duration

Re: Format textbox as a duration

Re: Format textbox as a duration

Re: Format textbox as a duration

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Formula: To calculate duration between two times, if duration is over 6hours subtract 30mi

Custom format to display numbers as duration

Help in converting duration to hrs format

Format cell to show duration of phone calls

Format cells for Duration?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions