• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Date Format Issues

R

ravikiran

Member
Dear Gurus,


I have a very typical problem with Date Format. I run an online report every day, export it into excel and then run it through a tracker. Once I download the file, I need to make some changes. I am planning to automate those tasks and am running into problems tackling the date format.


Downloaded file has 2 columns with Dates as below:


="06/07/2012 00:00:00"

="06/07/2012 00:05:00"

="06/07/2012 00:07:00"

="06/07/2012 00:09:00"

="06/07/2012 00:09:00"

="06/07/2012 00:12:00"


Every day I replace "="" with a blank and then """ with another blank so that the cells will be converted into 6th July - Date format.


But now, when I try to record a Macro and do the same with VBA, the Date and Month are flipped their positions. So instead of being 6th July, it is converted to 7th June, which is absurd.


Troubleshooting:

I tried to find if any thing is wrong with the Date format in Regional settings. But, Nope. It is set to UK Date and Time settings (DD/MM/YYYY).


Sample file:

Please find the sample file uploaded here. Thank you!

https://rapidshare.com/files/1235575107/PalletExport__5_.csv


Thanks in advance,


Regards,

RK.
 
Hi Ravikiran ,


I am not clear on what you are doing by replacing the "=" symbol and so on.


Have you tried using the DATEVALUE function ?


=DATEVALUE("06/07/2012 00:05:00")


gives 06 July 2012 correctly.


Narayan
 
Hi Narayan,


The report is developed in such a way that each value is presented as text in a formula.


="VALUE". For all data in the report. I am just converting it into numbers, dates and text to be able to use formulas on them for a dashboard.


I tried your suggestion and used this code:

[pre]
Code: 
Do Until ChkRow > LRow

ChkValue1 = DateValue(Cells(ChkRow, 3))
ChkValue2 = DateValue(Cells(ChkRow, 4))
Cells(ChkRow, 3) = Format(ChkValue1, "dd/mm/yyyy hh:mm")
Cells(ChkRow, 4) = Format(ChkValue2, "dd/mm/yyyy hh:mm")
ChkRow = ChkRow + 1

Loop
[/pre]

When I used this on --------- ="03/07/2012 00:04:00"

it is converted to 7th March instead of 3rd July.


Surprisingly, when I tried to use the formula in the spreadsheet directly, it gave the output as 3rd July correctly.


Sample file: Sorry for uploading the wrong file in my first post. Please find a sample file here: https://rapidshare.com/files/3415978642/Sample_-_Date_Format.csv


Thanks for all your help,


Regards,

RK.
 
Wow! That worked Narayan.


But I am loosing the Time, which the key point for my analysis.


How do I retain the time as well?


Regards,

RK.
 
Thanks Narayan.


I used the following code:

[pre]
Code: 
Do Until ChkRow > LRow

ChkValue1 = DateValue(Cells(ChkRow, 3))
ChkTValue1 = TimeValue(Cells(ChkRow, 3))
ChkValue2 = DateValue(Cells(ChkRow, 4))
ChkTValue2 = TimeValue(Cells(ChkRow, 3))
Cells(ChkRow, 3) = ChkValue1 & " " & ChkTValue1
Cells(ChkRow, 4) = ChkValue2 & " " & ChkTValue2
ChkRow = ChkRow + 1

Loop
[/pre]

Once I concatenate Date and Time, it is flipped from 6th July to 7th June :(


Unfortunately, I need both the date and time to be in one cell and I can't do away with that option. Isn't there any way to convert a text data into proper "DateTime" value.


="06/07/2012 00:05"


should converted (or replaced) as


06/07/2012 00:05


Thanks for all your patience.


Regards,

RK.
 
Hi Ravikiran ,


I am still confused ; let me tell you what I did , and what the results were :


1. My standard PC regional setting is English ( United States ) , in which the date format is mm/dd/yyyy.


2. When I open your CSV file , and format columns C and D , in date format , I get March 7 , irrespective of whether I format the cells as dd/mmm/yyyy or mmm/dd/yyyy.


3. When I format the same cells as numbers , the display is 40975.


4. Now , I repeat steps 2 and 3 , after changing my regional settings to English ( United Kingdom ) , in which the date format is dd/mm/yyyy.


5. The results of steps 2 and 3 are July 3 , and 41093.


6. It is clear that Excel takes the Regional settings as final ; whatever be the values in the .CSV file , according to one regional setting , Excel brings in the value 40975 , whereas , according to the other regional setting , Excel brings in the value 41093.


Whether now it is possible to change the fundamental value from one to the other , I have no idea.


I think that any DATE and TIME operations you do on this value would not in any way convert it from 40975 to 41093.


The only way is to separate out the month and date portions of the columns C and D cells , after converting them to text , recombine them in the desired date format , and then convert them to dates. After doing all this , if you add the times to the dates , you should get the right values.


Narayan
 
Dear Narayan,


I did go through all the posts just now and observed a bit of confusion. Sorry its my mistake not to observe that.


In both the files that I uploaded, the data you are looking at is a bit different. It is no more in text format. That happened because I saved the file before uploading it to rapidshare.


Please find the file as it is downloaded here: https://rapidshare.com/files/1920715343/PalletExport__7_.csv


I have 3 important columns in this worksheet. Date columns and the last column (Order Number) with 18 character. If I just save the CSV file and re-open it, the Date columns are automatically converted to the correct format. But the Order Number is where there is some data redundancy. Because excel doesn't support more than 15 digits, it is being converted with a trailing 0s, with which I can't identify the record.


So I need to convert all the columns from Text to their respective formats but still retain the Order number should be retained as text.


It would be very helpful if you can tell me the easiest way to do it. Probably I am surfing around but not getting it done the right way. I should have done this in the first instance itself. Sorry for the trouble.


Thank you,


Regards,

RK.
 
Hi Ravikiran ,


I am not facing any problems !


I opened your .CSV file in Excel , and it generated a sheet tab labelled PalletExport__7_


I ran the following procedure in this sheet :

[pre]
Code: 
Public Sub Convert_Dates_Times()
Dim data_range As Range
Set data_range = Range("C1:C134")
data_range.Select
row_counter = 0
For Each cell In Selection
d = DateValue(cell)
t = TimeValue(cell)
ThisWorkbook.Worksheets("Sheet4").Range("C1").Offset(row_counter, 0).Value = d + t
row_counter = row_counter + 1
Next
End Sub
[/pre]
Now when I formatted column C in Sheet4 , it showed the dates correctly as 06/Jul/2012.


Can you download the file from here and check the various tabs ?


https://docs.google.com/open?id=0B0KMpuzr3MTVN1d6cktySHpmY00


Narayan
 
Dear Narayan,


Thank you very much. Got my problem solved. Your code helped me find out the mistake. I am concatenating the Date and Time value instead of adding them up. Very stupid of me.


Anyways, thanks for your patience. And I learned a LOT today.


Regards,

Ravi.
 
You must log in or register to reply here.
Back
Top