Excel to Access time conversion issue

Here was my original question:

Excel time format conversion issue
 
Question:
I had an import problem.  I imported a time from one spreadsheet to another and got the format mixed up.  Unfortunetly, I don't have the old data and don't want to change 25K rows by hand.

Here is what the time looks like in my spreadsheet

0:47:00

This is 47 minutes

It should be this

0:00:47

Can I convert this?

Ok, the accepted answer worked by changing all of the cells to d:mm:ss.  Now I need to get it into Access in the same format.  How do I do that?
farmingtonisAsked:
Who is Participating?
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Connect With a Mentor Commented:
Please see this link, then let me know if you have any questions:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_22498310.html?cid=236#a18871665

mx
0
 
dirknibleckCommented:
if they are all minutes, write a formula that captures just the minutes:

=TIME(0,0,MINUTE(A1))
0
 
stanscott2Commented:
When you have an entry of 47 minutes, you just divide by 60 to get 47 seconds.

You can modify the Excel spreadsheet first.  If I had a column of these entries starting in cell A2, I'd first insert a blank column next to it, enter the formula:  =A2/60 and then copy it down, convert it to values with a range value and then copy it back to the original column.

Does this help?
0
 
BTogniettiCommented:
You can apply similar formatting to the Access field. A question first on the Excel format, d:mm:ss would display 0:47:00, if you were looking for 0:00:47, you would want d:hh:mm.

For the Access field, right-click the table > Design view > Select the field you wish to format > Select Date/Time from the Data Type drop down > Select Short Time from the Format Menu.

Good luck,

Bud
0
All Courses

From novice to tech pro — start learning today.