Hi please see attached spreadsheet, Please can you modify my formula in cell B3 in a way that the date in cell A3 can be entered in UK Format so it looks the same on both cells even though cell A3 has 00/\00/\0000 cuxtom cell.
date.xlsx
Thanks in Advance
youve lost me there!
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Hi and welcome to the forum
That might be a tough call. for instance, the "date" you provided...
1122014
is that 1-12-2014...Jan 12 2014 (US)...1 Dec 2012 (UK)
0r 11-2-2014...Nov 2 2012 (US)...11 Feb 2014 (UK)
How would excel (or us) be able to tell the difference?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Maybe this:
=--TEXT(A3,"0-00-0000")
Format as Date
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
date in A3 is US format, date in B3 uk format. i have setup A3 in a way that all i need to do is type date as 12232013 and it will auto change to a date type format, but as excel doesnt recognise this format as a date for use in formulas, ive found a code for it to convert to date cell B3 but this code only seems to work if A3 in US format. any ideas?
Thanks Tony, Your formula worked. issue solved
Last edited by JoeGellar; 11-09-2013 at 04:10 AM.
Good deal. Thanks for the feedback!
Just had to remove the -- partOriginally Posted by JoeGellar
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks