Convert Integer to Date

[seasidematt]

Hi,

Is it possible to convert a integer number into a date format?

I have a problem wheer my dates being input into a vba form that are showing as the integer date.

Is there some code that would validate and convert this number to a date format i.e. DD/MM/YYYY

Any help would be most appreciated

Matt

 

[MartinL]

howabout =text(a1,"dd/mm/yyyy")

 

[Richard Schollar]

Hi Matt

Have you just tried changing the number format of the cells to a date format?

 

[seasidematt]

Thanks But...<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
My scenario is i have a user form that is completed by our staff this is mirrored by using linked cells from the text boxes.<o></o>
<o></o>
When the users complete this form and I copy the data from the mirrored sheet behind into a master database<o></o>
<o></o>
I can then pre-populate the form if need at a later time but the date is converting to the integer no. <o></o>
<o></o>
I need some sort of VBA validation that would convert this back to a date format<o></o>

Matt

 

[RoryA]

Use CDate.

 

[seasidematt]

Thanks,

But to a vba novice how would i incorparte that into my spreadsheet

Thansk for your help so far

Matt

 

[RoryA]

Depending on which way you are going:

Me.Textbox1.Value = CDate(Range("A1").Value)

or:

Range("A1").Value = CDate(me.Textbox1.Value)

for example.

 

[seasidematt]

Hi thanks for this i think were on teh right track but i have added this into my code and cannot get it to work..

Private Sub datecomp_Change()
Me.datecomp.Value = CDate(Range("bc2").Value)
End Sub

Above is my code.. Am i doing anything incorrectly?

Cheeers
Matt

 

[RoryA]

It doesn't make sense to change the value from within the change event so i don't really know what you are doing.
I notice that you mentioned linked cells earlier - if you are using the ControlSource property to bind the textbox to the cell, I suggest you don't. Life is much easier if you load the data to and from the cells yourself.

 

[seasidematt]

Royra thanks for you help so far... <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Excuse my ignorance and complete un-understanding of vba 6 months ago i couldn’t tell you what vba stood for but after being thrown in the deep end I’m trying to produce a simple way of capturing data from our senior managers.. The old way was copying and pasting each individual cell into a database and could take up to 40mins per form and with over 400 forms and only myself i had to come up with a better way of getting the data into the main database.<o></o>
<o></o>
My only solution (Due to my limited knowledge and curiosity) I came across the control toolbox in excel i noticed that I could link cells from a form that a created on one sheet to another sheet that was hidden from public view.<o></o>
<o></o>
Once the user has sent this back to me I would unhide and copy the data in the second data sheet as this mirrored the database.<o></o>
<o></o>
Everything is working brilliantly and has saved me loads of time and given me a large pat on the back by my managers. <o></o>
<o></o>
Now the problem that i have is that data if i was to copy it back into the sheet to show on the form then the data format has been converted to the integer no i.e. 44095 its shows correctly on the hidden sheet but shows as integer on the form sheet<o></o>
<o></o>
So this is my problem and I’m sure there is some VBA that can overcome this.. <o></o>
<o></o>
Anyway thanks for your help so far<o></o>
<o></o>
Matt<o></o>