date conversion from Excel to Access 2

[KellyK]

Hi all,

I am importing an Excel spreadsheet into Access and need to make basic calculations on some date fields. Seems easy enough, but some of the formatting gets lost when I import from Excel to Access and I'm not sure how to keep it how I'd like. The date fields in Excel are formatted like [h]:mm:ss. For example, the date "1/19/1900 8:22:00 PM" is displayed as "476:22:00". Likewise, "1/18/1900 10:42:00 AM" is displayed as "442:42:00". I can add the two values to obtain "919:04:00". When I bring the records into Access, "1/19/1900 8:22:00 PM" is now displayed as "20:22:00", essentially dropping off the "hours" attributed to the 19 days past 1/1. Now, when I group by and sum these different values, I am not getting the correct values. Is there a way to get Access to behave the same way that Excel is? I hope this makes sense. Thanks in advance for any suggestions.

Kelly

 

[maxhugen]

How are you trying to group? By hour? Minute?

If you want a query that groups by hour (assuming you have records that span many days), add 2 fields to your query, something like:

cDay: CDate(Format([myDate],"dd/mm/yy"))

cHour: Format([myDate],"hh")

In the case of the calculated field 'cDay', I've used the Format() function to strip off the time, and then converted back to a date with the CDate() function. (If you don't convert back, it won't sort correctly, as the Format() function returns a string).

Max Hugen
Australia

 

[SkipVought]

""1/19/1900 8:22:00 PM" is displayed as "476:22:00". "

Date/Time is just a NUMBER. Tat number, in this instance, is 19.84861111; That is 19 days since 1/1/1900 plus .84861111 of a day, which is 20 hours and 22 minutes.

There is NO conversion problem, when it comed to dates, but you are not really wanting to convert a date/time. RATHER is is a DURATION of date/time. In this case 476 hrs, 22 minutes.

The DATA VALUE is there. it is ONLY the FORMAT that is causing you hearburn.




Skip,

_{I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent!}

 

[KellyK]

Skip, Thanks for your thoughts. Everything you're saying makes sense, however I still cannot do what I'm trying to do. Let's say I have the two dates I referenced in my post in Excel. I can add them to obtain a value of "919:04:00". Viewed as a date, this total is "2/7/1900 7:04:00 AM". Using those same dates in Access, if I add them, I obtain the value "2/8/1900 7:04:00 AM". Access must round up to the next date for each of the individual components? I have no idea, but it's making me crazy. I just need Access to mimic what Excel is doing. Adding is just step one, then I need to find percentages, and all of my percentages are off. Do I need to use some other function instead of a simple "add"? Thanks in advance for any additional suggestions you might have.

Max, I may use what you're suggesting, so I appreciate your help too. I am hoping I'm able to keep each of these dates in one date/time field and use them that way, but if not, your formulas will come in handy.

Thanks


Kelly

 

[SkipVought]

" Using those same dates in Access, if I add them,..."

There seems to be a severe disconnect between Excel and Access on date values.

You may need to subtract ONE from each Access date value...

[tt]
The following table illustrates how Microsoft Access stores Date/Time values: Double Date Actual Time Actual
Number Portion Date Portion Time
------------------------------------------------------------------
1.0 1 December 31,1899 .0 12:00:00 A.M.
2.5 2 January 1, 1900 .5 12:00:00 P.M.
27468.96875 27468 March 15, 1975 .96875 11:15:00 P.M.
33914.125 33914 November 6, 1992 .125 3:00:00 A.M.
[/tt]

http://support.microsoft.com/kb/130514

Skip,

_{I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent!}

 

[maxhugen]

Hi Kelly

As Skip pointed out, Access stores Date/Time values as a double. Access then does it's own formatting so that what you see in a table, query, form etc looks like a date/time. In addition, you can also format this value to display the value however you like.

My suggestion of using calculated 'fields' only applies to creating a query that sorts/groups - there is still only one Date/Time field in your table. To see how this works, try creating a new query. Don't add any tables, switch to SQL View, and paste in the following:

[tt]SELECT myDate, CDate(Format([myDate],"dd/mm/yy")) AS cDay, Format([myDate],"hh") AS cHour, CDbl([myDate]) AS cValue FROM myTable[/tt]

Of course, change the table and field names as appropriate, and run the query to see the resulting output.

I'm struggling to understand why you want to 'add' these date values together? What is it you're actually trying to achieve?

Cheers, Max

Max Hugen
Australia

 

[SkipVought]

"why you want to 'add' these date values together"

Because they are really DURATIONS;
[tt]
TotalDuration = Duration1 + Duration2
[/tt]

Skip,

_{I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent!}

 

[maxhugen]

Hi Skip, yes, I had suspected that, but why would you want to add (Total hours from 1/1/1900 to DateA) + (Total hours from 1/1/1900 to DateB)?


Max Hugen
Australia

 

[KellyK]

Thanks again for your help, guys. I am doing a project for a transportation company. I need to import an Excel spreadsheet into Access and combine the data with some other sources. One component of this is to measure the idle percent for a driver's truck. I am given the drivers "road" hours in a format like "623:30:00" - the truck was moving 623 hours, 30 minutes for the quarter. In a different column is the "idle" hours in a similar format: "101:18:00". These values are stored as date/times, 1/25/1900 11:30:00 PM and 1/4/1900 5:18:00 AM, repectively. The problem is twofold: I need to group by driver # (there are multiple lines per driver), using SUM as the function to get total road hours and total idle hours. Then in order to find the idle %, I have to take idle hours / (road + idle hours). The second part seems easier to work around by splitting out the components and adding them as Max has suggested, but grouping by driver will be tricky. Looks like I'm probably going to have to do some subtotals in Excel prior to bringing the sheet into my Access database. I was hoping there was an easier way - this is a process that is going to be repeated and I am working to automate it. Thanks again.

Kelly

 

[SkipVought]

I'd suggest doing it ALL in Excel.

Use MS Query to obtain the Access data and use MS Query to join tables (sheets) together in Excel.

faq68-5829

Skip,

_{I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent!}

 

[KellyK]

Thanks for the ideas. Unfortunately I've put a lot of effort into developing this Access model and with a deadline looming, I don't know that I have time to recreate the wheel. I will keep it in mind for future reference though. Cheers.

Kelly

 

[maxhugen]

Hi Kelly

A problem is being introduced because the spreadsheet you are provided with is expressing something that should be a numerical value as a date. "623:30:00" should really be 623.5 etc.

I'd go see the person who creates this spreadsheet and ask if they could provide it in a correct data type, ie as a number. However, I can understand that this might not be possible...

Soooo... what I'd look at doing is converting the 'date' in Access back to how its actually stored internally - as a double - and converting that to hours.

The stored value in Access is in days, and fractions of days. Thus your example "1/25/1900 11:30:00 PM" is stored internally as 26.9791666666667. To the left of the decimal point are whole days - 26 days - and to the right, its the fraction of a day, or 23.5/24 (actually its converted to seconds, so it would really be 23.5*60*60 / 24*60*60).

Sorry if all this sounds like a mouthful, but once you understand how the date is stored and what it means, it's quite easy to manipulate.

In the query example I showed you in the previous post, I converted the value that Access presents to you back to its internal format by changing it to a double: CDbl([myDate]).

This gives you the value in days, so all that's needed is to multiply by 24 to give hours: CDbl([myDate])*24

Now its easy to perform the sum operations you need to do!

For example, paste this into the SQL View of a new query (changing the field/table names to suit):

[tt]SELECT DriverID, Sum(CDbl([myDate])*24) AS cdate
FROM myTable
GROUP BY DriverID[/tt]

HTH, Max


Max Hugen
Australia

 

[SkipVought]

"... expressing something that should be a numerical value as a date. "623:30:00" should really be 623.5 etc. "

Yes and no. It WOULD probably be a good idea to CONVERT your durations (In DAYS) to HOURS, for instance, as Max suggested. The provlem is in Access' conversion of the Time "623:30:00" to DateTime, it seems.

If you do the conversion before the import, just store as FLOATING POINT and interpret as hours.
[tt]
hrs = days * 24
[/tt]


Skip,

_{I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent!}

 

[KellyK]

Hey guys,
I decided just to take each of the dates/durations and convert them into a field for days, a field for hours and a field for minutes in Access. Then I will get a grand total for each duration in minutes so that I can calculate percentages. A little tedious, but at least then I'll be able to group, sum, divide, etc. to my heart's content. Thanks so much to both of you.


Kelly