Date/Time Function in Excel (Day/Month/Year & time)

**tortuga32** · 12-13-2011, 03:55 PM

Hello,

I need assistance, please (anyone!!).
I have a spreadsheet where I have run a query and pulled an export to Excel. The report is very simple (below). Basically, I am trying to convert the second column from Day/Month/Year to Month/Day/YEAR with the allocated time listed. If I go and paste this information into excel (since it is an export) there are constraints in the cell that do not allow me to do the general format options and change the column into the correct format. Nothing works.
Any help here?

Created Updated Resolved
12/13/2011 13/12/11 16:07 12/13/2011 0:04:00

**tortuga32** · 12-13-2011, 04:00 PM

Sorry to make it clear...
my column is
A = 12/13/11
B = 13/12/11 16:07
C = 12/13/11
D= 0:04:00

The focus is column B -- It will not convert

**NBVC** · 12-13-2011, 04:09 PM

Possibly there's an easier way...but you can add a temporary helper column formula:

=DATE("20"&MID(B2,7,2),MID(B2,4,2),LEFT(B2,2))+TIME(MID(B2,FIND(" ",B2)+1,2),RIGHT(B2,2),0)

copied down

then copy and paste special|values over the original.

**tortuga32** · 12-13-2011, 04:16 PM

Wow!!!
That worked.. so I have to do that into another cell... so that the dates are transformed!!
Wow!!!
Thank you!

**tortuga32** · 12-13-2011, 04:23 PM

Okay.. NOt working..
Help...

**NBVC** · 12-13-2011, 04:23 PM

Excel is seeing that combination as a text string and therefore you can't "reformat" as date.

An alternative that you can try, if you don't mind splitting the date and time into 2 separate columns...

First, insert a column before column C.

Then select column B and go to Data|Text to Columns.

Select Delimited, click Next,
Select Space checkbox, and click Next,
Select Date radio button from the column data format area and then select DMY from the adjacent drop down
Click Finish.

You can re-combine the date and time again after, if desired.

**tortuga32** · 12-13-2011, 04:24 PM

Would it be easier to post a copy of the spreadsheet?

**NBVC** · 12-13-2011, 04:25 PM

Try above, if feasible first, then if not, post the worksheet, void of confidential info.

**tortuga32** · 12-13-2011, 04:40 PM

See attached.
The focus is Column J, K, L.
Should be easy but not sure why it isn't working.

**NBVC** · 12-13-2011, 04:50 PM

When I open your workbook, all 3 cells show up as proper dates and time... all showing 9/12/2011 and a time.. and they are all formatted as custom: m/d/yyyy h:mm

Dates isn't one of my strong suits... especially in translation from UK to US formats... so not sure what the issue is.

**NBVC** · 12-13-2011, 04:54 PM

If still trouble and you don't want to do the Text to Columns, try helper formula adjusted:

=DATE(MID(K2,7,4),MID(K2,4,2),LEFT(K2,2))+TIME(MID(K2,FIND(" ",K2)+1,2),RIGHT(K2,2),0)

**tortuga32** · 12-13-2011, 04:54 PM

No... The dates are wrong... it is in DAY/MONTH/YEAR format...
I need them in
"MONTH" / "DAY" / "YEAR" format...
So what was mentioned 9/12/11 --- that should be December 9, 2011 --- the system is coverting it once it is extracted to Excel.
That is my entire problem.
Help!

**tortuga32** · 12-13-2011, 04:57 PM

I should have posted a different example

Please see attached.

**NBVC** · 12-13-2011, 04:59 PM

Well this original formula worked for me to convert that to November 28th:

=DATE("20"&MID(K2,7,2),MID(K2,4,2),LEFT(K2,2))+TIME(MID(K2,FIND(" ",K2)+1,2),RIGHT(K2,2),0)

**tortuga32** · 12-13-2011, 04:59 PM

I tried =DATE(MID(K2,7,4),MID(K2,4,2),LEFT(K2,2))+TIME(MID(K2,FIND(" ",K2)+1,2),RIGHT(K2,2),0)

in the spreadsheet and it does not work.
Please open the attachment and see if you are able to do it. It does not work.

**NBVC** · 12-13-2011, 05:03 PM

Check formula in CI2. Does it not appear right?

Alternatively have a look at how something similar was resolved here: http://www.excelforum.com/excel-gene...te-format.html

**tortuga32** · 12-13-2011, 05:05 PM

Yes, this formual works : DATE("20"&MID(B4,7,2),MID(B4,4,2),LEFT(B4,2))+TIME(MID(B4,FIND(" ",B4)+1,2),RIGHT(B4,2),0)
However, it takes out the time? How can I add the time in the column back in there?

**tortuga32** · 12-13-2011, 05:09 PM

To addk to the above...
when I use: DATE("20"&MID(B4,7,2),MID(B4,4,2),LEFT(B4,2))+TIME(MID(B4,FIND(" ",B4)+1,2),RIGHT(B4,2),0)
it works in some fields and in others it does not... because the dates show up as 1/12/11 but it should be 12/1/11. Also, the times do not populate.
Is there a way that it can get populated correctly.
I basically have 500+ rows.. where dates are listed as DD/MM/YY--- and some are showing up as 1/12/11 but it should show up as 12/1/11 - when I use DATE("20"&MID(B4,7,2),MID(B4,4,2),LEFT(B4,2))+TIME(MID(B4,FIND(" ",B4)+1,2),RIGHT(B4,2),0) it tells me I have an error in my cell. Also, my time range is not being captured.
Is it possible to fix?

**tortuga32** · 12-13-2011, 05:14 PM

another attachment with the two examples.
Sorry!
I appreciate everyone's help here.
Just need to figure out how to solve this.. and carry the time's in the same cell.

**protonLeah** · 12-14-2011, 02:00 AM

You can use a UDF with a helper column:

Please Login or Register  to view this content.

Date/Time Function in Excel (Day/Month/Year & time)

LinkBack

Thread Tools

Rate This Thread

Display

Date/Time Function in Excel (Day/Month/Year & time)

Re: Date/Time Function in Excel (Day/Month/Year & time)

Re: Date/Time Function in Excel (Day/Month/Year & time)

Re: Date/Time Function in Excel (Day/Month/Year & time)

Re: Date/Time Function in Excel (Day/Month/Year & time)

Re: Date/Time Function in Excel (Day/Month/Year & time)

Re: Date/Time Function in Excel (Day/Month/Year & time)

Re: Date/Time Function in Excel (Day/Month/Year & time)

Re: Date/Time Function in Excel (Day/Month/Year & time)

Re: Date/Time Function in Excel (Day/Month/Year & time)

Re: Date/Time Function in Excel (Day/Month/Year & time)

Re: Date/Time Function in Excel (Day/Month/Year & time)

Re: Date/Time Function in Excel (Day/Month/Year & time)

Re: Date/Time Function in Excel (Day/Month/Year & time)

Re: Date/Time Function in Excel (Day/Month/Year & time)

Re: Date/Time Function in Excel (Day/Month/Year & time)

Re: Date/Time Function in Excel (Day/Month/Year & time)

Re: Date/Time Function in Excel (Day/Month/Year & time)

Re: Date/Time Function in Excel (Day/Month/Year & time)

Re: Date/Time Function in Excel (Day/Month/Year & time)

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions