Display Time over 24 Hours

A

aftabn10

Registered User.

Local time

Today, 11:47

Joined

Nov 4, 2008

Messages

96

• Nov 5, 2009

• #1

Hi, I was wondering how i would display Time Values that are over 24 hours in Access. Currently within Excel I have 3 values:

35:06:40
47:26:15
01:25:30

When importing this table with these values I get the following:

01/01/1900 11:06:40
01/01/1900 23:26:15
01:25:30

From what I can see, the 24 hours that is missing has been added onto the date. How can i get this to display values over 24 hours?

Thanks in advance.

 

N

namliam

The Mailman - AWF VIP

Local time

Today, 12:47

Joined

Aug 11, 2003

Messages

11,695

• Nov 5, 2009

• #2

You cannot, even in excel it is "shakey" and the "raw" value is as you see it in the table.

24 hours = 1 day
48 hours = 2 days
50 hours = 2 days and 2 hours

You cand do some "trickery" as a date is basicaly a number ... and a whole number is 1 day.
12 hours is 0.5 day etc...
Thus doing TimeField * 24 will return the digital number of hours you are looking for

Format ( Timefield * 24, "00" ) & Format(Timefield,"NN:SS")

will display the proper time, problem here is data entry... as this is display only.

 

The_Doc_Man

Immoderate Moderator

Staff member

Local time

Today, 05:47

Joined

Feb 28, 2001

Messages

29,819

• Nov 5, 2009

• #3

Depending on the version of Access, you might have a new format specification for times that wasn't always available in older versions: hhh:mm:ss (as a format string) will specify the kind of translation you seek. Not all versions of Access have this. Sorry, don't recall where it first appeared.

 

N

namliam

The Mailman - AWF VIP

Local time

Today, 12:47

Joined

Aug 11, 2003

Messages

11,695

• Nov 5, 2009

• #4

Definatly not in 2002
?Format(now()-date()+1, "HHH:MM:SS")
1515:11:16

Just repeating the 15 for 3 pm, instead of showing the expected 39 hours

Nice to know though

 

U

uneek78

Registered User.

Local time

Today, 06:47

Joined

Mar 26, 2009

Messages

68

• Nov 5, 2009

• #5

If you wanted to do this in excel I could tell you how, because I currently have this working in excel. I receive the difference of D:HH:MM from 2 columns that have date/time in them. I'm having a headache trying to do this in access. I believe once you pass that 24-hour period that's when you have to start VB stuff. Of which, I know none.

 

U

uneek78

Registered User.

Local time

Today, 06:47

Joined

Mar 26, 2009

Messages

68

• Nov 5, 2009

• #6

This is the code to do it in excel:

=TEXT(E9-D9,"d:h:mm")

You have to make sure you have the date/time in the correct format in the columns the information is coming from. Calculates perfect every time. Access is a jerk! Lol! Just kidding, but access is powerful and difficult.

 

N

namliam

The Mailman - AWF VIP

Local time

Today, 12:47

Joined

Aug 11, 2003

Messages

11,695

• Nov 6, 2009

• #7

namliam said:

You cannot, even in excel it is "shakey" and the "raw" value is as you see it in the table.

24 hours = 1 day
48 hours = 2 days
50 hours = 2 days and 2 hours

You cand do some "trickery" as a date is basicaly a number ... and a whole number is 1 day.
12 hours is 0.5 day etc...
Thus doing TimeField * 24 will return the digital number of hours you are looking for

Format ( Timefield * 24, "00" ) & Format(Timefield,"NN:SS")

will display the proper time, problem here is data entry... as this is display only.

Click to expand...

Hav3e you tried my earlier suggestion?