Negative Time values when export Access Query to Excel

[yolo]

The time difference in access query works very fine in 18:00 (Short Time) format , but when i export to excel it came out with "########" and the value is -0.14679132479

how to solve this problem , i keep trying but it doesnt work

PLEASE HELP ME >< Thanks a LOT

 

[Galaxiom]

Time is held as fractions of a day which is the number you are seeing in Excel.

Format the Excel column to Time.
In the example you gave (0.14679132479) the time would be 3:31:23 AM

 

[yolo]

Time is held as fractions of a day which is the number you are seeing in Excel.

Format the Excel column to Time.
In the example you gave (0.14679132479) the time would be 3:31:23 AM

i Tried to change the Time column to "time" but it doesnt work :/.....

 

[Galaxiom]

Seems unlikely but is the 0.14679132479 arriving in Excel as Text?
That is the only thing I can think of that would prevent it being formatted to Time.

Normally though this would show a warning as a little triangle in the corner of the cells with a message about numbers being formatted as text when you hover over the cell.

What versions of MS Office are you using?

What command are you using for the Export?

 

[yolo]

Seems unlikely but is the 0.14679132479 arriving in Excel as Text?
That is the only thing I can think of that would prevent it being formatted to Time.

Normally though this would show a warning as a little triangle in the corner of the cells with a message about numbers being formatted as text when you hover over the cell.

What versions of MS Office are you using?

What command are you using for the Export?

Version 2010

ExportwithFormatting in MS Access Macro

 

[Galaxiom]

Works fine for me in Office 2010.

I suspect the problem might be something corrupted in the table.
Perhaps try recreating the table and appending the data from the old table.

Watch out for Name Auto Correct. If you rename anything the new name will be propogated through the database if NAC is enable which it is by default.

 

[Mihail]

The "########" in Excel appear because the width of the column is to small. So enlarge that column.
After you format the Excel column as time, double click in a cell (in order to enter in Cell Edit Mode) then press ENTER (without doing anything else). Is this action make the Excel cell to show you the right value ?

 

[yolo]

The "########" in Excel appear because the width of the column is to small. So enlarge that column.
After you format the Excel column as time, double click in a cell (in order to enter in Cell Edit Mode) then press ENTER (without doing anything else). Is this action make the Excel cell to show you the right value ?

Result is still the same .....

 

[yolo]

Works fine for me in Office 2010.

I suspect the problem might be something corrupted in the table.
Perhaps try recreating the table and appending the data from the old table.

Watch out for Name Auto Correct. If you rename anything the new name will be propogated through the database if NAC is enable which it is by default.

I recreated but still the same problem ....

 

[Mihail]

Can you to upload the Excel workbook ? Put a color in that column.

 

[yolo]

Actually what im doing is , i need to calculate the hours difference within the same date or some are different date.

So i wrote this in Query Field

Tool Down: [Up Time]-[Down Time]

and in the Criteria

DateDiff("h",[Up Time],[Down Time])>=3

then i change the Format to "Short Time"

 

[yolo]

Can you to upload the Excel workbook ? Put a color in that column.

Actually what im doing is , i need to calculate the hours difference within the same date or some are different date.

So i wrote this in Query Field

Tool Down: [Up Time]-[Down Time]

and in the Criteria

DateDiff("h",[Up Time],[Down Time])>=3

then i force change the Format to "Short Time"

 

[yolo]

Can you to upload the Excel workbook ? Put a color in that column.

this is the Extracted Excel
View attachment Query1.xls

 

[Mihail]

Interesting.
If the format is General or Number, I can see the values, but as soon as I format as Date or Time the column become ############.
And I think that Excel has wright. You have calculated a difference, not a time.
What I don't understand is why Access format that field as time ?
And what you (we) must ask here is how to say to Access to format that field as Number.
Maybe someone can help you (us, because I have no idea how to do).

 

[Mihail]

One more question: The values in Excel are correct ?

 

[yolo]

One more question: The values in Excel are correct ?

in value the excel value is same as access , but in time it doesnt

 

[ChrisO]

Maybe this will help:-

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

Chris.

 

[mariyakeon]

Hey Yolo
I you start this process manually it would be time consuming and its not necessary that data will be correct. To remove this problem or error You need to export access query to Excel without any loss of database.
In such situation Use Access To Excel Conversion Tool which facilitates with advance techniques and algorithms to export access query to excel in easy manner. Type in Google Access To Excel Conversion and get advance solution to resolve your query.