using IIf function for division in Access??

[Maven4Champ]

Ok, what I am trying to do is this:

My time fields in my spreadsheet are a decimal format. I need to divide each individual cell by 86400 to convert it to a seconds decimal format. Then my Access data format of hh:nn:ss will take care of the rest.

I currently am using an IIf function in another time conversion for an hourly field. I want to know if I can use an IIf function to do the statement above on these other fields and if so, what might it look like?

In Excel, I simply divide the columns of data by 86400. What way would I do a similar function like this within Access?

 

[Maven4Champ]

For instance,

My Column field is named: Avg Inbound ACD Time. This is from table named tblImport. I want any numerical data in this column to be divided by 86400.

Would that require an IIf function?

I see where you are saying : ? format((x mod y)/y, "hh:nn:ss")

But does that do the job? Also is this an IIf function or an expression etc?

Thanks

 

[scriverb]

Maven4Champ: I see nowbody is jumping in on your question here. Probably because of the ambiguity of the problem. We are all probably a little confused by your thread. Is this spreadsheet being linked to an ACCESS database? If so what kind of field type is being indicated. Please exaplain your data a little clearer so that we may try to help.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[Maven4Champ]

Ok..

Previously in Access, to get the correct formatting of the times, I would divide by 86400 (how many seconds there are in 1 day).

Here is my problem. In my existing database, times were already converted in the mm:ss format, so when I applied my formatting of hh:nn:ss in Access to the time columns, the times already converted to mm:ss show up now as hh:ss.

So in an effort to prevent this in the future, I want to be able to modify the data I am importing by dividing certain columns of data (i.e. Avg Inbound ACD Time) by 86400 so when the hh:nn:ss is applied, it shows 00:01:15 instead of 01:15:00 as it does now.

Does that make any sense?

 

[Crowley16]

eh, the data that you are importing, why don't you just set it as datatype time, that'll preserve the actual time values despite what format you use...

as for calculations in columns, you can just use normal division...
select x/y from tbl where...

 

[OhioSteve]

Okay, now we are learning alot more about this issue.

This is my understanding of the situation:

1) You have data in an external source with the format mm:ss.

2) You are importing this data, and you want the field to have the format hh:mm:ss.

Here are my questions for you:

1) How are you "importing" the data? Are you using the file/get external data menu option, the transfer text macro action, cut & paste, vb, or some other technique?

2) What is the data type and format of the source and destination fields? One of the date/time formats is "long time". Is that the format of the destination field?

3) Will the imported data ALWAYS be less than an hour?