Can't formar number to time

[dramadeur]

So there are a bunch of fields with time values in them, showed like: "6:20 PM", "10:00 PM" etc

\1

If I average them by a certain field, they get to be displayed as number (serial?).
I tried "CDate", "TimeSerial", "Format(xx, "h:nn:ss")" - and for all of them I was getting errors (like data mismatch).

Does anyone know how to convert this to time? I need them to be displayed as "5:30 PM".

p.s. okay, one of the solutions is to insert: "h:nn AM/PM;@" in the format line. But is there another way?

[Crooza]

After you use your average formula which will look like this

=AVERAGE(A1:A3)

simply right click and format as time then choose HH:mm

** OK just realised this was access not Excel - BUT Formating should be the same.

[FDibbins]

Time is a decimal of 1 (day), so for instance 06:00 AM is actualy just 0.25, 12 noon is .05 and 06:00 PM (18:00) is 0.75. Again, excel formats it to something we see as a time

It sounds like you just need to format your answers as time?

[alansidman]

With your query open in design view, right click on the field. Click on properties. In the format field, type hh:nn:ss

[dramadeur]

After you use your average formula which will look like this

=AVERAGE(A1:A3)

simply right click and format as time then choose HH:mm

Have you read post scriptum in the original post? I know about field properties. I was looking for another way. Maybe through formulas, for instance serial number 0.75 is equal to 6PM, right? So if a formula multiplies 0.75 by 24, then somehow changes it to 6 and adds "PM" at the end, that'd be it. Adding "AM/PM" shouldn't be hard since you get a number within 1-24 range (e.g. iff X is <12 then AM, else PM).

It sounds like you just need to format your answers as time?

Yes.

With your query open in design view, right click on the field. Click on properties. In the format field, type hh:nn:ss

See post scriptum in the original post. I had already posted this solution. Give me another.

[Crooza]

Ok. If I was looking for help I'd probably phrase my responses a little differently. Good luck

Feel free to use 'thank you' and 'please' too at any time