Hi, mcb1180!
I hoped that my previous post or then NARAYANK991's one had helped you. But being wrong, let me try again.
The DateValue function is supposed to convert text dates into a serial number (isn't it)
Yes, it is. But only for text that represent a valid date according to the regional configuration settings (if no cell format specified) or the cell format (if specified, which then rules).
Example:
System/format with DMY, cell with "20/12/2010", DATEVALUE works
System/format with DMY, cell with "12/20/2010", DATEVALUE doesn't work
System/format with MDY, cell with "20/12/2010", DATEVALUE doesn't work
System/format with MDY, cell with "12/20/2010", DATEVALUE works
System/format with DMY, cell with "12/11/2010", DATEVALUE works as Nov 12th
System/format with MDY, cell with "12/11/2010", DATEVALUE works as Dec 11th
but since my dates are text dates keyed UK style it's not working for date >=13 (i.e. 15/5/2013 20:13:15 give #value error)
What should match is your regional settings / cell format to the way dates are keyed in, no matter where you are or what date config you use (I use too DMY), cells format should match with cell contents.
Example:
System with DMY
A1: 20/12/2010, date (not text), cell format dd/mm/yyyy, it's Dec 20th, 2010
B1: 12/20/2010, date (not text), cell format mm/dd/yyyy, it's Dec 20th, 2010
C1: "20/12/2010", text, cell format doesn't matter, DATEVALUE works, it's Dec 20th, 2010 (E1)
D1: "12/20/2010", text, cell format doesn't matter, DATEVALUE doesn't work (F1)
Check worksheet Sheet1.
This inability to convert the dates has of course led me to discover that none of the other formulas within the worksheet actually work anymore. (teach me to build formulas in a dummy worksheet)
The inability is relative since Excel via the DATEVALUE function has the ability to convert
dates. Where
dates are text values, not numeric values or real dates, and text values which correspond to the date type set at the regional configuration, regardless how did you formatted the cell since it holds text values and not numeric/date values.
SirJB7,
I'm must be the biggest dunce in the world, but I can't got this to work in my document, I can't get it to work in your document.
I've tried formatting the cells, I've tried using the format painter, I've tried the convert text to columns trick. Nothing seems to make my dates actual dates, the stay "text" and even though you managed to get the formula to work as text, when I copy it into my file or try and copy my file into to your to preserve the formatting it still doesn't work.
Will you please take one more look and see if you can see where I'm going wrong. Because honestly I'm really starting to doubt my intelligence and problem solving abilities. I'm not great with Excel, but I've Never had this much trouble.
Check worksheet Compilation (3).
I did this:
a) Selected column A and verified that all cells had the same format: General
b) Idem B, Date English US d-mmm-yyyy
c) C, Date dddd, mmmm dd, yyyy
d) D, Custom dd-mmm-yy
e) E, Date English UK d mmmm yyyy
f) F, Date English US d-mmm-yyyy
g) added G2: =B2 and copied across thru K2 and down as required
h) formatted columns G:K as General
i) added L2: =NO(O(CONTARA($G2:$K2)=0;SI.ERROR(Y($G2=$H2;$G2=$I2;$G2=$J2;$G2=$K2);FALSO))) -----> in english: =NOT(OR(COUNTA($G2:$K2)=0,IFERROR(AND($G2=$H2,$G2=$I2,$G2=$J2,$G2=$K2),FALSE)))
and copied down as required
j) added a CF (conditional format) condition to yellow for columns G:K depending on L
k) added autofilter to all columns
l) checked that there were errors (L, true) but then revised the formulas and weren't coherent
m) changed formula in B2 from:
=DATEVALUE(DAY(A2) & "/" & MONTH(A2) & "/" &YEAR(A2))
to:
=DATEVALUE(DAY($A2) & "/" & MONTH($A2) & "/" &YEAR($A2))
and copied across thru E and down as required (leaving column F formulas as they were, or almost)
n) added N1 and P1 cells as old and new separators for use replacing "." and "/" in column F formulas to match actual regional config settings (if different from those values), changing formula in F2 from:
=IF(ISNUMBER(A2),A2,SUBSTITUTE(REPLACE(MID(A2,4,3)&A2,7,3,""),".","/")+0)
to:
=IF(ISNUMBER(A2),A2,SUBSTITUTE(REPLACE(MID(A2,4,3)&A2,7,3,""),$N$1,$P$1)+0)
o) filtered worksheet for errors (#¡VALUE! or different dates), i.e., TRUE in column L
And this is the result:
1) Your formulas weren't correctly copied thru columns B:E, not they're equal and the calculations are correct.
2) Debraj's formulas only worked for text values (i.e., not valid dates as per cell format) in column A, only if they were in the format MDY.
So, please give a look at the fixed from the below link, and just advise if any issue.
https://dl.dropboxusercontent.com/u...is formula_ (for mcb1180 at chandoo.org).xlsx
Regards!
