Solved

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

Posted on 2015-01-06
17
101 Views
Last Modified: 2015-01-30
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
0
Comment
Question by:agwalsh
  • 5
  • 5
  • 4
  • +1
17 Comments
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 40533887
see attached.
ee.xlsx
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 40533890
put this in cell and copy down,  change the range format to "short date"

=DATEVALUE(MONTH(A2) & "/" & DAY(A2) & "/" & YEAR(A2))
0
 
LVL 18

Expert Comment

by:SimonAdept
ID: 40533902
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
 

Author Comment

by:agwalsh
ID: 40534019
@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
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 40534033
Can you send me your file that has error? I can check what is causing the error.
0
 

Author Comment

by:agwalsh
ID: 40534126
Here it is.. thank you :-)
ee-jim-jam-vers-01.xlsx
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 40534266
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
 
LVL 21

Assisted Solution

by:Ejgil Hedegaard
Ejgil Hedegaard earned 125 total points
ID: 40534305
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 25

Assisted Solution

by:ProfessorJimJam
ProfessorJimJam earned 125 total points
ID: 40534367
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
 

Author Comment

by:agwalsh
ID: 40534379
@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
 
LVL 21

Expert Comment

by:Ejgil Hedegaard
ID: 40534410
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
 
LVL 18

Expert Comment

by:SimonAdept
ID: 40534439
>@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
 
LVL 21

Expert Comment

by:Ejgil Hedegaard
ID: 40534467
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
 
LVL 18

Expert Comment

by:SimonAdept
ID: 40534474
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
 

Author Comment

by:agwalsh
ID: 40544721
@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
 
LVL 18

Accepted Solution

by:
SimonAdept earned 250 total points
ID: 40545354
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
 

Author Closing Comment

by:agwalsh
ID: 40579310
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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now