dates not working with formulas. Mix of US and UK dates

hi Folks
Got an issue. Someone got a data dump. Some of the dates are formatted in different ways and now their formulas are not working. I've attached an example. Have tried formatting it with text formats (only works with some), tried formulas. Still not working. Any suggestions. Have tried custom format as well...would appreciate suggestions. Thanks. dates-not-working-with-formulas.xlsx
agwalshAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
SimonConnect With a Mentor Commented:
Hi, please see updated version that includes the time element.
ee-Mixed-DateFormat-Cleanup-v2.xlsx
I've added this as a second worksheet (the ...v2 one).
0
 
ProfessorJimJamCommented:
see attached.
ee.xlsx
0
 
ProfessorJimJamCommented:
put this in cell and copy down,  change the range format to "short date"

=DATEVALUE(MONTH(A2) & "/" & DAY(A2) & "/" & YEAR(A2))
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
SimonCommented:
I assume you're working on a PC with UK regional settings? Excel tries to be too smart and converts to valid UK dates where possible.
Is the original data dump in Excel format or something else? If original is non-Excel, import using the delimited file wizard and set that column to text so you can work on the values consistently.
Another workaround might be to set your system to US date/time settings first.
0
 
agwalshAuthor Commented:
@Professor JimJam...I tried that in my original file and copied it down..and I got value error messages...see attached. Although (annoyingly!) I can see that your one worked fine :-). Any ideas?
@SimonAdept - original data dump was US, AFAIK, I tried converting the data using the text to columns and got the same issue again....but will pass on both your suggestions..
0
 
ProfessorJimJamCommented:
Can you send me your file that has error? I can check what is causing the error.
0
 
agwalshAuthor Commented:
Here it is.. thank you :-)
ee-jim-jam-vers-01.xlsx
0
 
ProfessorJimJamCommented:
agwalsh,

I am confused. what are you trying to achieve here? can you please elaborate?   you sent back my file.
I want the following info for me to understand what exactly you are trying to achieve

I quote back your original excel file uploaded initially.

you have column A which has a label header of submission date, then you have column B and C and D and E are these helper columns?  then column F  can you put there the desired date and format manually so that I understand what are you looking for.  

because when I look in your original formula, it has lots of mistakes which confusing, for example check cell F4 which you have all the reference to cell A4 while there is inconsistent reference of cell F11 . can you clarify why have you used F11 "LEFT(A4,LEN(F11)-2)" ?
0
 
Ejgil HedegaardConnect With a Mentor Commented:
The formula =DATEVALUE(MONTH(A2) & "/" & DAY(A2) & "/" & YEAR(A2))  also fails for me.
Changing to =DATEVALUE(DAY(A2) & "/" & MONTH(A2) & "/" & YEAR(A2)) to fit my date format, fails on the AM/PM values.
This formula extracts year, day and month to put in the date function, using search for / to find the positions.
=IF(OR(RIGHT(A2,2)="AM",RIGHT(A2,2)="PM"),DATE(VALUE(MID(A2,SEARCH("/",A2,SEARCH("/",A2)+1)+1,4)),VALUE(LEFT(A2,SEARCH("/",A2)-1)),VALUE(MID(A2,SEARCH("/",A2)+1,SEARCH("/",A2,SEARCH("/",A2)+1)-SEARCH("/",A2)-1))),DATE(YEAR(A2),MONTH(A2),DAY(A2)))

Open in new window

ee-jim-jam-vers-02.xlsx
0
 
ProfessorJimJamConnect With a Mentor Commented:
Ejgil,

in my machine, both =DATEVALUE(MONTH(A2) & "/" & DAY(A2) & "/" & YEAR(A2)) and =IF(OR(RIGHT(A2,2)="AM",RIGHT(A2,2)="PM"),DATE(VALUE(MID(A2,SEARCH("/",A2,SEARCH("/",A2)+1)+1,4)),VALUE(LEFT(A2,SEARCH("/",A2)-1)),VALUE(MID(A2,SEARCH("/",A2)+1,SEARCH("/",A2,SEARCH("/",A2)+1)-SEARCH("/",A2)-1))),DATE(YEAR(A2),MONTH(A2),DAY(A2)))   shows the same value.

what is your machine region format?
0
 
agwalshAuthor Commented:
@Professor JimJam...the problem is that formulas such as weeknum etc do not work with these dates. I have attached a file with the original dates (as copied and pasted) and if I want to extract weeknum out of either of them...(original or your date) - it doesn't work consistently. I've attached a file to show this....
ee-jim-jam-vers-02.xlsx
0
 
Ejgil HedegaardCommented:
The region is DK.
A date like 14/1/2014 (Jan 14) is recognized with the formula =DATEVALUE(DAY(A2) & "/" & MONTH(A2) & "/" & YEAR(A2)), also with time values, with or without AM/PM, since the time part is ignored by the datevalue function, but for some reason US format 1/14/2014 does not work.
0
 
SimonCommented:
>@SimonAdept - original data dump was US, AFAIK, I tried converting the data using the text to columns and got the same issue again....but will pass on both your suggestions..

Is the original data dump an Excel spreadsheet or delimited text? I think the issue is Excel's insistence on trying make valid dates out of things that look like dates, regardless of whether it has to switch the day and month elements to do so.

If you do text to columns from a text delimited file AND CHOOSE 'TEXT' for the data type of that column you should be able to avoid the problem. You can then use a consistent set of string functions to pull out the day month and year elements.

Once the file has already been opened in Excel it is very difficult to recover from the mixture of US and UK dates, because you can't work out whether 5/6/2012 is 5th June or 6th May.

You can use the assumption that all apparently valid dates are actually transposed US ones... I've done that in the past, but you need to have sight of the raw date data to be sure that you're ending up with the correct date. It's relatively easy if you know the data dump is already sorted in date order, as you can then check whether your conversions produce rows of ascending date, but if this is not the case it needs careful checking.
0
 
Ejgil HedegaardCommented:
Depending on how you want to work with week numbers, observe that the Weeknum function always starts week 1 on Jan 1, and week 2 the following Sunday (type=1) or Monday (type=2), so week 1 typically has less than 7 days.
1/1/2012 is in week 52, and not in week 1.

To get the ISO weeknumber use this formula, date in A1.
=INT((A1-DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,3)+WEEKDAY(DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,3))+5)/7)
0
 
SimonCommented:
Please see sample attached and let me know if this is what you need.

The only formula you may need to change (after checking whether the values that Excel thinks it has converted to dates are correct) is the one in column F.

I've used six columns to get to the final date. It could be condensed, but the way I've done it makes it easy to check the intermediate results, and those cols can be hidden.
ee-Mixed-DateFormat-Cleanup.xlsx
0
 
agwalshAuthor Commented:
@SimonAdept - aka Excel Wizard person :-) Passed this on and person DELIGHTED!!  However she has one other question....(and yes, I did point out that it wasn't me :-))
However, I also need the time added to a date as I measure a cycletime i.e. time between two date/time stamps. If I only have a date, I’ll get a rounded qty of days when I need a more accurate measure. See example in Rows1 and 2 below of two date/times and the cycletime between both. If the dates are then used without the times (rows 3 &4), it will give very different results. Do you have magical formulae that can extract the same UK format “FinalDate” as per your previous spreadsheet but also add in the time from the US format?
 
See file attached.
dates-cycletime-vers-01.xlsx
0
 
agwalshAuthor Commented:
Thanks to all of you. Have awarded main points to SimonAdept because his solution was closest to what was required. But appreciate input of ProfessorJimJam and Ejgil Hedegaard .
0
All Courses

From novice to tech pro — start learning today.