Solved

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

Posted on 2015-01-06
17
102 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:Simon
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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:Simon
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:Simon
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:
Simon 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
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 Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

910 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

22 Experts available now in Live!

Get 1:1 Help Now