Read Excel file Date
Hello,
I am building a GUI which reads an other Excel file.
The read file contains some date, when I get these data, I have some integer instead of date.
How should I proceed ?
For example, the file I read have the following date :
2005/4/1
When I get it in VBA, I obtain : 38443. Can this integer be converted into the initial date ?
I have the same question for the time :
0:05:00
When I get the data, I obtain :
0.00347222222222222
Thank you in advance.
Last edited by oro77; 01-23-2012 at 05:48 AM.
Excel stores dates in a numerical value representing the number of days elapsed since 30th December 1899 (assuming not using the 1904 date system). To derive the date that this value represents you could use this:
Dim myDate As Date myDate = 38443 MsgBox myDate
use
msgbox format(384430.00347222222222222,"dd-mm-yyyy hh:mm:ss")
Thank you very much, it works perfectly.
I have an additional question :
How do I format the date correctly, my GUI get the data and display it on my sheet.
I have taken the "0:05" data from the original sheet and it automatically puts it like : "12:05:00 AM"
How to keep the same format : "0:05" ?
Thank you in advance.
Last edited by oro77; 01-23-2012 at 05:03 AM.
Are you placing the date back into an Excel sheet or into a textbox or something similar (ie not an Excel cell)? Assuming it is a cell, then you just need to directly place the extracted value in the cell and amend the formatting of the cell to display the date how you want. if the latter, then you can use the Format function to construct a string to the required format:
Me.TextBox1.Value = Format(myDateTime,"yyyy-mm-dd hh:mm")
Thank you, it works. I placed my data in Excel cells.
Declare the variable as date and not as integer or long.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks