Adding date to cell with time through userform

**peakymatt** · 06-01-2010, 02:05 AM

Hi All

I have a Userform that has become quite complex. The problem I am having is this:

Two of the elements of the form are a Start Time and Date. I need the form to accept the input as 06021400 ie mmddhhmm.

Then for this to translate into the Excel Sheet to display just hh:mm.

Is there any way I can do this. I have tried a multitude of things, the last being:

"VariableName = Format(Date, "mmddhhmm")"

however had no joy

Please help if possible.

Thanks

Matt

**Leith Ross** · 06-01-2010, 02:22 AM

Hello Matt,

When you say "Userform" Are you referring to a worksheet or a VBA UserForm?

**peakymatt** · 06-01-2010, 02:24 AM

Sorry, should have said that.

VBA UserForm, which then puts the data into a worksheet

**Leith Ross** · 06-01-2010, 02:49 AM

Hello peakymatt,

Assuming your initial value "06021400" was input using a text box, you can simply remove the hours and minutes portion using string functions like this...

Dim myTime As String

  myTime = Right(TextBox1.Value, 4) 

 'Remove this line if you don't want the colon
  myTime = Left(myTime, 2) & ":" & Right(myTime, 2)     'myTime = "14:00"

  Range("A1").Value = myTime

**peakymatt** · 06-01-2010, 07:54 PM

Hi thanks for that

I must be doing something wrong. I have added the code to my UserForm

and when I enter the value in the textbox and submit the form all I get back in the Spreadsheet is "#########.

Any ideas??

**peakymatt** · 06-01-2010, 08:03 PM

Hi Further to my last

Have got it to returna result, the entry detail was "02061900" the result was "10190000". Any ideas how I can get the output to be the same as the input, with the output tio display just as hh:mm.

Thanks

**Leith Ross** · 06-01-2010, 08:08 PM

Hello peakymatt,

I think I should have a look at your workbook. Can you post a copy for me?

**peakymatt** · 06-01-2010, 08:23 PM

The work is on a secure system, so can't actually send it. Will try and do up a dummy to replicate my problem.

Stand by, and thanks again

**peakymatt** · 06-01-2010, 08:59 PM

Hi Again

Here we go. This will give you an idea of what I am trying to achieve.

Person ID in Col A, Start Time in Col B, Stop Time in Col C. Then as you can see there is a days worth of times across the top, set up in 30 min increments. When the times are entered from the Form they go into COL B and COL C, and then it is conditionally formatted over the time period. Dates for the times has to be current date, then when it clocks over midnight the date is obviously tomorrow.

All data is on the sheet named timeline. Activate the form by the button towards the bottom

Hopefully you can help

Thanks