MS ACCESS convert Hours to Days?

Question

Hi.

I have created a query from a table called TimeWorked (one column). It contains data in hours:minutes (or hh:nn). I need to convert this to Days only.

I am trying something like:

Days_Only: Format([TimeWorked]/1440,"dd")

but i keep getting "#Error" in my columns after I run the query.

Anyone have any ideas?

Under Total in the Query screen I have tried adding the TimeWorked column as Group By, and the Days_only . . .etc entry as "Expression". The query takes longer to process, but the same result.

I have also tried switching the "dd" to "nn" (out of curiosity), but still the same thing. I KNOW I am missing something small!

I should also mention that the Data Type for the "TimeWorked" table is actually setup as a 'text' instead of 'number (tried converting to number but then all my hh:mm data turned to 0s).

Thanks, I will try both your suggestions later today when I get a chance!!!

So far so good--only issue is that if I have an entry that is 0:4 (so anything under 24 hrs) I get an #error entry, but that is OK, as I can count those as 0 day periods.

Answer 1

I did some experimenting in Access, and how about this:
(Remember to ignore the line breaks, just trying to prevent Yahoo from chopping off what I typed)

Days_Only: Format(CDbl(
Left([TimeWorked],
Len([TimeWorked])-3)/24+CDbl(
Right([TimeWorked],
2))/1440),"0.00")

This will display the time in days, rounded to two decimal places. I hope this at least gets you closer to what you're looking for.

Answer 2

SELECT CDbl(TimeValue([Table1]![TimeWorked])) AS Days_Only
FROM Table1;


Provided your text data is formatted in hh:mm you can use the TimeValue function which will convert the text data type to the date/time data type.

Converting the TimeValue to a double data type will display the time as a fraction of a day in decimal format.

Microsoft handles dates and times by representing days (date) as whole numbers and the time as a decimal. The display of the date/time is just formatting of this underlying number...

Specific dates are representas as the number of days since 1/1/1900 = 1.0
12 noon is 12/24 or .5
12 noon on 1/3/1900 = 3.5
Today is:
39241.80841 = 6/8/07 7:24 PM