Solved

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

Posted on 2015-01-06
17
106 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 26

Expert Comment

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

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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

 

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 26

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 26

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
 
LVL 26

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel 2010 VBA - Color codes for selection.interior.color = 3 46
A Table within a User Form 24 43
Transposing this formula ... 5 12
Excel Macro 9 18
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

856 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