TimeValue

[muffntuf]

I am trying to pull a time from a text field that will convert to a time value. It is in military time 24:00. The formula I am using is:

Time:timevalue(format([timefield],"@@\@&quot

This catches everything except any time from 00 hours to 1:00 am (midnight to one o'clock in the morning). So if the time is :40 I am getting #Error, everything else converts fine.

How do I get the formula to catch the 00 hours to 1:00 am?

Thanks!

 

[SkipVought]

Hi,

Why wouldn't you use...

timevalue(format([timefield],"hh:mm")

?
Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

sorry...

if you're in Access then minutes are formatted...

timevalue(format([timefield],"hh:nn")

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[muffntuf]

Skip,

Thanks I have tried the "hh:nn" format. It does not pick up the 00 to 59 minutes. It is only picking up the the hour and minutes. 1:49 PM.

Any other ideas?

Thanks,
Muffntuf

 

[SkipVought]

I do not understand. I went into an Access database and formatted a date/time. Then in a query formatted and expression to display ONLY the time.

My source data was
1/1/2001 00:49:00

My result was
00:49

using...
Expr1: Format([StartDate],"Short Time&quot

as the format (which I entered as
Expr1: Format([StartDate],"hh:nn&quot Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

You do know that Time values in Excel and Access are values between 0 and 1.

So 01:00 is 1/24 or 0.041666667

BYW, military time does not use AM and PM. Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[muffntuf]

You are right. Military time does not, but it shows up automatically. I think the problem you are missing is the format condition. This field is a text field not a date/time field, and it only contains the times. That is why I am having problems bringing all of the times. So I get everything that is past 1:00 am in the morning to work, just not the 00 to 59 minutes.

 

[SkipVought]

I think that you need to convert the text value to a time value, per...

TimeValue = [hours]/24 + [minutes]/60/24 Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[muffntuf]

bS_TIME: (Format(TimeValue([bstime]),=hh/24:nn/60/24))??