View Full Version : How to subtract number of hours from 24 hour timezone in alphanumeric format
atulsanwal22
07-02-2020, 11:21 PM
Hi All,
I am working on a project where i need to get the values of a CCY curve for past 6 timesnaps for example if i opt for USD rates for Jul 3, 2020 London 12PM (L1200) then it should give the rates for today L1200,L1100,L1000,L0900,L0800,L0700 (rates will be fetched using excel orcale database connection using select query) which i have achieved using the below two formulas
=LEFT(B3,SEARCH("L",B3))&(VALUE(MID(B3,SEARCH("L",B3)+1,1000))-100)
=IF(LEN(B4)=4,LEFT(B4,1) & "0" & MID(B4,2,100),B4)
But the issue comes when i opt for L0400 or before that because if i opt for L0400 timezone and use the mentioned formula it gives the below result which is incorrect. the last two cells should be L2300 and L2200 from previous day
Incorrect
L0400
L0300
L0200
L0100
L0
L-100
Attached is the output i am expecting. any help on this would be much appreciated.
26899
Why are you shipping midnight (2400 / 0000)
atulsanwal22
07-03-2020, 12:12 PM
pardon me, i am not clear with your comment
In F3 you have 1AM... In G3 you have 11 PM... What about 12AM... You skipped an hour... Is that deliberate?
atulsanwal22
07-03-2020, 07:39 PM
In F3 you have 1AM... In G3 you have 11 PM... What about 12AM... You skipped an hour... Is that deliberate?
Yes thats deliberate
Alright, this is going to take a little setup on your part. First Format Your Row 3, and any other similar use Rows in your preferred Date Format.
Paste the below code into the sheet's Code Page. I set the Constant "atuNumTimeSnapsToConvert" to 6, but you can change it as needed
Next, Enter into Cell B4 , or any other cell used for this purpose, the desired starting Date+Time, Example "7/4/2020 2:33:45AM". Do not start at midnight, the code will fail. Only the hours part of the time is relevant, the Mins and Secs can be any value
Doubleclick cell B4, or any other cell used for this purpose, to start the code. There is no testing to ensure you double click a proper cell.
Option Explicit
Private Const atuNumTimeSnapsToConvert As Long = 6
Private Const LHour00 As String = "Lhh00"
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
GenerateTimes Target
End Sub
Private Sub GenerateTimesOriginal(ByVal Target As Range)
'Tested: Does not skip midnight
Dim i As Long
For i = 1 To atuNumTimeSnapsToConvert - 1
Target.Offset(, i) = DateAdd("h", -i, Target)
Target.Offset(-1, i) = Target.Offset(, i)
Next i
Target.Offset(-1) = Target
End Sub
Private Sub GenerateTimes(ByVal Target As Range)
'Tested: Skips Midnight
Dim A As Long
Dim i As Long
Dim C As Long
A = MidnightCheck(Target)
For i = 1 To atuNumTimeSnapsToConvert - 1
C = i
If i >= A Then C = i + 1
Target.Offset(, i) = DateAdd("h", -C, Target)
Target.Offset(-1, i) = Format(Target.Offset(, i), LHour00)
Next i
Target.Offset(-1) = Format(Target, LHour00)
End Sub
Private Function MidnightCheck(ByVal Target As Range) As Long
Dim i As Long
MidnightCheck = atuNumTimeSnapsToConvert + 1
For i = 1 To atuNumTimeSnapsToConvert - 1
If DatePart("h", DateAdd("h", -i, Target)) = 0 Then
MidnightCheck = i
Exit Function
End If
Next i
End Function
In A1: L0400
In B1:7/3/2020
In C1: =$B$1+(--MID($A$1;2;2)-(ROW($A1)-1+(ROW($A1)>--MID($A$1;2;2))))/24
in C2: =$B$1+(--MID($A$1;2;2)-(ROW($A1)-1+(ROW($A2)>--MID($A$1;2;2))))/24
etc.....
atulsanwal22
07-11-2020, 01:38 AM
cant we use the below select case :think:
26908
atulsanwal22
07-11-2020, 01:40 AM
cant we use the below select case
Sub selectcase()
Dim time As String
time = Range("B3").Value
Select Case time
Case L1200: Range("C3").Value = "L1100"
Case L1200: Range("D3").Value = "L1000"
Case Else: Range("C3:D3").Value = "Invalid"
End Select
End Sub
Please repost without colors
Sub selectcase()
Dim time AsString
time = Range("B3").Value
SelectCase time
Case L1200: Range("C3").Value ="L1100"
Case L1200: Range("D3").Value ="L1000"
CaseElse: Range("C3http://www.vbaexpress.com/forum/images/smilies/astrosmiley.gif3").Value ="Invalid"
EndSelect
EndSub
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.