add a # of hours to a time?

[wolf.stalker]

hi all and thanks for any help.

doing somethign a bit crazy here. writting a macro that takes a time, converts it from it's local time to my time, then adds a number of hours to it.

i get a report that has any number of rows of data and each one has a time stamp from it's own local time. i need to covert that to MY time zone (done) and then add on number figure (currently int value rounded to no decimal places). this is where i am getting into trouble.

i have been trying to use TimeValue("xx:xx") but dosen't seem to work my time value is always changing. any suggestions?

eg

1) current time is 18:05 PST
2) my time is 16:05 CST
3) i want to add say between 0-24 hrs to this time to come up with new time...any ideas?

[Aussiebear]

Post your workbook so we can see what your working with. Ensure personal data is removed when doing so.

[wolf.stalker]

bah..message was corrupted when i tried to post :-\ will try again shortly

[Aussiebear]

What do you mean "message was corrupted"?

[Bob Phillips]

Have you tried TimeSerial(16,0,0) for instance?

What form does the data come in? What is your code that transorms it?

[wolf.stalker]

Have you tried TimeSerial(16,0,0) for instance?

What form does the data come in? What is your code that transorms it?

when i said it was corrupted, i meant i posted a msg here and it was all kinds of crazy formatting...whole page of "code" when it was mostly just like this....chat stuff...

anyway with that being said..here is my "data" that starts in Colmun B. it's all string except the Add hrs which is integer value.


My Tm ZoneConverted TimeReport TimeAdd hrs20:2118:2118:21 PST2

my macro takes the "Report Time" and coverts it to "Converted Time" (drops the PST) then using the TimeValue("xx:xx") fucntions, converts (based on local Time Zone + my Time Zone) to My Tm Zone. This all works and works fine.

Now what i want is to get a NEW time that is "My Tm Zone" + "Add hrs". So the asnwer would be say 22:21. This is where i am hung up.

In looking at the TimeSerial function, i am not sure how i could use this here. would i have to break down "My Tm Zone" to hrs and mins and then insert THAT into the function? could that work? I will try and play with this, but can something like NewTime = TimeSerial(MyHr-AddHr,MyMin,00) even work?

[wolf.stalker]

Have you tried TimeSerial(16,0,0) for instance?

What form does the data come in? What is your code that transorms it?

omg i just spen 15 min posting/explaing and then posted it....and it didn't post

[wolf.stalker]

ok...tring for a 3rd time to post something /fail

[Paul_Hossler]

Bit brute force, and has more hard coding that I'd like

[vba]
Option Explicit
Function AddHours(FormattedTime As String, HoursToAdjust) As Date
Dim iLocalOffset As Long
Dim sWorkingTime As String
Dim dtWorkingTime As Date
Dim iHours As Long, iMinutes As Long, iSeconds As Long

Select Case Trim(UCase(Right(FormattedTime, 3)))
Case "PST"
iLocalOffset = -2
Case "MST"
iLocalOffset = -1
Case "CST"
iLocalOffset = 0
Case "EST"
iLocalOffset = 1
'etc

Case Else
iLocalOffset = 0
End Select
sWorkingTime = Left(FormattedTime, Len(FormattedTime) - 3)
sWorkingTime = Trim(sWorkingTime)
sWorkingTime = "=TIMEVALUE(" & Chr(34) & sWorkingTime & Chr(34) & ")"

dtWorkingTime = Application.Evaluate(sWorkingTime)

AddHours = TimeSerial(Hour(dtWorkingTime) + iLocalOffset + HoursToAdjust, Minute(dtWorkingTime), Second(dtWorkingTime))
End Function


Sub drv()
MsgBox Format(AddHours("18:05 PST", 4), "hh:mm:ss")
End Sub
[/vba]


There are a number of obvious generalizations that can be made

http://www.sql.org/sql-database/post...al/x11950.html

has the list of TZ codes and their offesets from GMT, so you could easily generalize the function to handle converting from any TZ to any other


Paul

[SamT]

Put this formula where you want the new time to appear:

=TIMEVALUE("2:00")+A1

Changing A1 to the old time Cell and "2:00" to the Hours:Minutes you want to add. You need the quotes around the time.

SamT

[wolf.stalker]

Sam -- Thanks for the idea, but my timevalue is not static and thus the "02:00" wouldn't work. I am looping this some 10-100 times so that hour value could be 0-10 hrs.

Paul -- i will throw your function in the mix and see how is stands up. i will need to make a few mods though. for one, my report dosen't only give me 3 letter TZ, but in 1 case, i get a 4 letter AKST. go figure. anyway, i have that part already figured out so i can use what i wrote in place of what you have.

i think where i am going to focus my attention is here as this looks to be where the meat of the answer is. now if i can just wrap my head around whats going on here :-) and maybe learn something!

[vba] sWorkingTime = Left(FormattedTime, Len(FormattedTime) - 3)
sWorkingTime = Trim(sWorkingTime)
sWorkingTime = "=TIMEVALUE(" & Chr(34) & sWorkingTime & Chr(34) & ")"

dtWorkingTime = Application.Evaluate(sWorkingTime)

AddHours = TimeSerial(Hour(dtWorkingTime) + iLocalOffset + HoursToAdjust, Minute(dtWorkingTime), Second(dtWorkingTime))
[/vba]

[Bob Phillips]

It just takes a time string, strips the seconds off, and then converts that to a number before adjusting for your time zone and additional hours.

[Bob Phillips]

You can also do it in one line

[vba]
Dim AddHours As Date


AddHours = Application.Evaluate("=--""" & Trim(Left(FormattedTime, Len(FormattedTime) - 3)) & """") + _
(iLocalOffset + HoursToAdjust) / 24
[/vba]