Time values changing from excel to access - NEED HELP

[Maven4Champ]

I am running into a big problem when importing data from a spreadsheet and into an Access database. Currently, I have columns of data which display time of minutes/seconds in the mm:ss format. For instance, on row in a column might say 00:34 which means 34 seconds.

Upon importing it into Access, the Default Date/Time is set for the Data Type in my table. The format of the Date/Time is Medium Time - however this changes the value shown in my report and in my table as 00:00 or when I click inside the cell it shows its value as 12:00:53 AM.

Is there any kind of formula or modification I can make within Access to keep my value of 00:34 in tact w/out it changing?

 

[mdmilner]

How exactly are you importing this data into Access?

Mike

 

[Maven4Champ]

How exactly are you importing this data into Access?

Mike

File>Get External Data>Import -

Then I import the .xls - set the first row as the column header and go next next finish and it imports it.

TimeLine: Format([TimeIn],"Short Time") & "-" & Format([TimeOut],"Short Time") & (DateDiff("n",[TimeIn],[TimeOut])/15) & " Qtrs"

and instead of it importing the time as mm:ss it imports it as hh:mm:ss Short Time in Access which causes me to lose the seconds essentially.

Again, for instance my Avg External Call Time value will show as 0:12 in an Access cell.
Upon clicking inside the cell, the value shows at 12:12:09 AM. I need it to show 12:09 or 00:12:09 as the value instead.

 

[Maven4Champ]

It appears "for now" that I might have solved my own question...

If I go into Table Properties and set the Data Type as Date/Format - I am then given the format options specifically for that data. instead of choosing medium time, long time, short time, date, etc. I typed in mm:nn:ss and saved it. I then opened back up the Table in datasheet view and it now reports it as hh:mm:ss with the minute and second variable in tact!

Was this the best way to do it?

 

[Maven4Champ]

CentreVue Time Format


Here is my example...
Notice how on the right, the time lists correctly from AM to PM?

On the left, in the first column, it reads it as AM when it should read PM.

Now here is something. Our reports are from 7am to 7pm - maybe sometimes 7:30pm

Is there a way or code to set it where anytime 1:00/2:00/3:00/4:00/5:00/6:00 is present in the spreadsheet, it is recognized as PM instead of AM? I say this because those times will never be in our report because again, our report starts at 7am so those times have already passed in AM?

I figure I could do it either in Excel or Access - whichever is possible?[/url]

 

[mdmilner]

That's perfect.

 

[Maven4Champ]

That's perfect.

??? - I was hoping you would have had an answer? lol

Anyone else that can help me, it would be greatly appreciated!

 

[mdmilner]

Go back two posts. Looks like you were typing before I'd left my note.
If I understood your post, you'd fixed the problem by formatting the field.

Aka, the correct data was there, it just wasn't displaying properly.

"That's Perfect" fits because

1) You did it yourself
2) Field formatting is the easiest way to fix things. Lots of time you don't need complex programming for tasks that really can be simple.

Mike

 

[Maven4Champ]

Go back two posts. Looks like you were typing before I'd left my note.
If I understood your post, you'd fixed the problem by formatting the field.

Aka, the correct data was there, it just wasn't displaying properly.

"That's Perfect" fits because

1) You did it yourself
2) Field formatting is the easiest way to fix things. Lots of time you don't need complex programming for tasks that really can be simple.

Mike

Yes, I did fix it by putting 0.5 in a cell in Excel, then copying, Paste Special (check Values and check Add) and then it sets them to PM.

My lingering problem is my boss wants the export of this data from CentreVu and Import into Access to be as seamless as possible (not messing around in Excel much).

So my question is, is there a way to format this data after it has been imported into Excel and is being read as AM when it is really PM?

If so, that would be the best route.

I import the data from Excel, and apply a formatting or run a macro or module or something that applies certain conditions or formats or formulas to the data so it is seamless (instead of editing individual records in Excel before importing...

Does that make any sense or am I talking myself in a circle?

 

[Maven4Champ]

Is it possible to create an IIF function that does the following

When 'Time To' equals PM value, change 'Time From' to PM value if value is after 1:00 or greater?