MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Needs Help on Time conversion using VBA

  Asked By: Adelisa    Date: Mar 02    Category: MS Office    Views: 1356

I have a VBA code that look like this:
"SELECT V9000_TFloat.UTCTime, V9000_TFloat.Value" & Chr(13) & "" & Chr(10) &
"FROM xaJtdb.dbo.V9000_TFloat V9000_TFloat" & Chr(13) & "" & Chr(10) & "WHERE
(V9000_TFloat.UTCTime>{ts '2004-09-23 00:00:00'} And V9000_TFloat.UTCTime<{ts
'2004-09-23 00:10:00'})" & Chr(13) & "" _
, "" & Chr(10) & "ORDER BY V9000_TFloat.UTCTime")

However, the UTCTime on my database is the universal time (GMT) and what I
need is our local time which is GMT+8.
I'm a greenhorn when it comes to VBA and my guess is to use the DATEADD or
CONVERT commands. Can somebosy help me with this?



1 Answer Found

Answer #1    Answered By: Garrett Brooks     Answered On: Mar 02

I don't understand your code, but there is a much simpler way.
Internally MS Office uses a number for dates and times (ONE number
which contains both: a number for the date, a decimal point, a
number for the time). this sounds complicated, but it allows very
simple additons. I do not know what format your database  uses.
However, here are the 2 options. Let's assume you have
GMT = "10:00:00 PM"

1. If you have an Excel cell which contains the time  in the date
format the formula is:
GMT_plus_8 = Format(GMT + TimeSerial("8", "0", "0"), "hh:mm:ss

2. If you have an Excel cell which contains the time in the text
format, you have to convert the text first into a date. In this case
the formula is slightly different:
GMT_plus_8 = Format(TimeValue(GMT) + TimeSerial
("8", "0", "0"), "hh:mm:ss AM/PM")

The result in both cases is
GMT_plus_8 = "06:00:00 AM"

As said, MS Office uses ONE number for both, the date and the time.
Thus it is crucial that you use the Format function to get the time
only. If you omit the Format, MS Office would show the result as a
date plus the time.

Didn't find what you were looking for? Find more on Needs Help on Time conversion using VBA Or get search suggestion and latest updates.