![]() ![]() ![]() The ROW($1:$30) doesn't apply to spreadsheet rows. What was the purpose of the Row 1 through 30 range, but more specifically-why did you stop it at 30 rather than row 2373? Those are some clever formulas!Ĭurious-I notice your use of ROW($1:$30) nested within the larger date converting formula, although the formula was copied and operates down all 2373 rows. Quite the head scratcher, but I REALLY appreciate the help. Both formulas either passed over or dropped non-date text. However, FlameRetired's formula flipped the months & days for numeric dates, while working well for text dates. Month & day are flipped, FlameRetired had the wrong year & AlKey had or no result/text conversionĪlKey was blank/skipped FlameRetired had Month & Day flipped and dropped the textĪlKey was blank/skipped FlameRetired was #N/AĪlKey had a great first pass, while FlameRetired picked up many of the dates that were entered as text and extracting the text date that had other non-date text in the cell. If you find a solution in the sample file, please keep the Count column so I can match the data to my master file.Īgain, any thoughts are greatly appreciated! Thank you for the time and consideration. ![]() Unfortunately, I don't know VBA, but I might be able to follow a solution that uses it.Īny thoughts on how this can be cleaned up? It would be great to have the dates in (any format) one column and any 'extra' non-date text in a separate column.įor rows that only show a year (or month & year), I don't mind using a 01 proxy for the missing day (or month & day).įor any instances that have more than one month listed, it doesn't matter to me which one is kept and the other can be dropped. Many text conversion formulas found on this and other forums, but most just produced #Value results. Text-to-Columns to help separate and format each piece, but again the mixed formats caused trouble. matching the 4 categories above and testing to see which rows are text v numeric data:Ĭell formatting features, but it didn't work for me due to the different text & numeric format types in each cell. Lastly, there are a few random rows that are not date information at all or has non-date text mixed with date info-the non-date text doesn't belong in the date column and needs its own column.īelow is an example of the messy dates with Cat. Also, in many rows, the date info contains inconsistently formatted alphanumeric data that I haven't been able to nicely identify and convert.Ĥ. Again, not so bad to fix, but is troublesome given the other type of date data in the column.ģ. Some date info looks numeric (MM-DD-YY), but is text. That isn't so bad to fix, but is complicated with the other date data entries.Ģ. Most of the dates were entered as MM-DD-YY (or YYYY), although Excel is reading them as DD-MM-YYYY. I have 4 categories of dates to get into a clean, common format.ġ. No luck after many hours of fiddling with it.Īny help is immensely appreciated! I've extracted the dates into a sample file and attached it here. My goal is to get all the dates into some sort of common format and I can later use for sorting and in other functions. While nearly all of the rows have a date, the formats vary widely. This data has nearly 2400 rows, where each row is a donation and should have a date. I can only imagine how many volunteers entered the data using their own data entry preferences. I'm helping a nonprofit organization with their records and came across a VERY messy dataset for their donations. There are LOTS of posts regarding Excel date formatting (172 pages or so) but tons of skimming, I didn't see my situation-so apologies in advance if this is a duplicate thread. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |