• 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.

Big issue with date format

M

mediatx

Member
Hello Everyone


From an excel data query, I receive thousands of rows containing dates in various formats.

The dates imported are useless for me as I receive following date syntax:


03.01.2010 (my default format = dd.mm.yyyy)

1/3/2010 9:00:00 AM (m/d/yyyy ...)

01/03/2010 9:00:00 AM (mm/dd/yyyy ...)

01/3/2010 9:00:00 AM (mm/d/yyyy ...)

1/03/2010 9:00:00 AM (m/dd/yyyy ...)


I need to have all data displayed as dd.mm.yyyy (default format for my locale) without time stamp.


I cant use text import wizzard and using lefT/right/trim so far brought only useless data, as, depending on the format, the counters wont work will trim too much or not enough.


Any idea how this can be cleaned?
 
Interesting...


You can use formulas to clean this.


Assuming the imported date is in cells A1:A100...


write =DATE(VALUE(MID(A1,FIND(" ",A1)-4,4)),VALUE(MID(A1,FIND("/",A1)+1,FIND("/",A1,FIND("/",A1)+1)-(FIND("/",A1)+1))),VALUE(SUBSTITUTE(LEFT(A1,2),"/","")))


This should work for the above 4 date formats you have mentioned... a bit lengthy, but works all the same.
 
Hello Chandoo, thanks for taking the time and answering, partially, it seems to work BUT there seem to be an issue


11/29/2010 9:00:00 AM returns 11.05.2012

8/23/2010 9:00:00 AM returns 08.11.2011


so the format looks great, but for any reason the months values are now days and the day values reflect anything I cant match at all.


Maybe this helps a bit better:

The column format for all entries is dd/MM/YYYY

howeve,r I still get dates within cells that show american format as mentioned above (months/day/year) your formula brings the correct format, but the wrong dates.


Can you help me to get this sorted?
 
If the format is dd/mm/yyyy hh:mm am/pm then it should be

Code: 
=DATE(MID(A1,FIND(" ",A1)-4,4),LEFT(A1,FIND("/",A1)-1),MID(A1,FIND("/",A1)+1,FIND("/",A1,FIND("/",A1)+1)-FIND("/",A1)-1))
 
I have a problem with date function like I have got 01-10-2010 10:45:00

I need it in dd/mm/year format and I have used the formula '=DATE(YEAR(MID(E2,7,4)),MONTH(MID(E2,4,2)),DAY(LEFT(E2,2)))'


and I need time formula also can you please let me know
 
Pradeepthota


You don't need to use the Year, Month and Day functions within the date Function

Date assume they are in that order


So to use your formula use:

=DATE(MID(E2,7,4),MID(E2,4,2),LEFT(E2,2))


and for the time:

=DATE(MID(E2,7,4),MID(E2,4,2),LEFT(E2,2))+(MID(E2,12,2)/24)+(MID(E2,15,2)/(24*60))+(RIGHT(E2,2)/(24*3600))


For the time you need to convert each part into a fraction of 1 (1 day)

so

10 Hrs is 10/24

45 Mins is 45/(24*60)

00 Secs is 00/(24*60*60)
 
Is your date in the format DD-MM-YYYY ?

as the formula wont work if it is D-MM-YYYY or DD-M-YYYY
 
Hi hui,


Thanks it helps me a lot.


I have another question regarding the same

'IF(H159="third",(F159-11/24),F159)'

F159=13-10-2010 01:16:00

now i have used this for some its ok but some others it showing value error please help me on this.


'IF(H157="third",(F157-11/24),F157)'

F157=12-10-2010 20:09:00 for this it has worked but for the previous it is showing an error . if you have an alternative formula for this can you help me
 
I'd check that the values are actually dates, easiest way is retype them manually eg: 12-10-2010 20:09:00


Also make sure that third doesn't have a leading or trailing space

It doesn't care about case eg: third = Third = THIRD


The formula is ok and works in a number of samples I tried
 
Yeah it works but what if the data is huge then we cannot do it manually.


Can you please suggest some other formula for this.
 
Hi Hui,


I have a VBA code problem


I have to insert a row after the name of every person( which is merged) in the sheet R1 and under the dates respectively I have to take the log data and insert under the same in the inserted column from r2 can you please help me on this VBA code.


Can you let me know where can I upload my work sheet
 
Pradeepthota


Please start a new post for new questions, that way people with relevant experience will assist. Often people won't go back to an ongoing post if they don't think they can add value, but if the subject has changed they will be unaware of the change


You can have several active posts at once.


To upload files have a read of: http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Hi Hui,


Sorry for that ,will do that next time and thanks for giving me the URL I have uploaded the sheet if some one can help me its great
 
When you uploaded the file Rapidshare will give you a download link

You have to post the link to the file here so we can see it
 
Pradeepthota


1. You want to insert a Row after every merged Name in Sheet R1

2. You then want to transfer the Shift data and Log details for the appropriate date back to R1 for each day


questions:


Why do you want a macro to insert the rows? You only need to do that once and only have a few rows or do you have to do it regularly eg every month or will you clean out the data for a new month


The data can be transfered with formula and doesn't need a macro


Can you please clarify
 
HI Hui,


the data will be done for every month and the records are higher.


For every new month i will get a new set of data.


I want to transfer the Shift data and Log details for the appropriate date back to R1 for each day according to the name.


if you can help me with formula and a macro that will be great learning and also help ful me reduce my work by half as it is a repetitive job every month
 
Pradeepthota


I have changed all the dates from Text to Dates


Have a look at this

http://rapidshare.com/files/430979434/pradeepthota_Hui_V1.xlsx


Can you let me know what you think?

Do you need to insert extra lines?
 
Hey Hui,


Thanks a lot

That wonderful it could be very much useful to me and you saved my day.


I need to insert a row completely after each employee id till the end of it and insert the log value for each date of the respective employee id.


can you please help me on this with VBA CODE
 
You must log in or register to reply here.
Back
Top