Number format issue

Hello all

I have a macro in excel that generate a txt file for all rows with data.

But i have an issue with 1 single value

In my cells from column E, starting at row 7, i will have a value with this format:
201204081900 = YYYYMMDDHHMM
hrs would
The isssue i have is that i would like to add 7 hours to it. This should also respect the date.

Ex: If date is 20120408 and time at 2300, adding 7 hrs would be 201204090600.

How can i do that?

It just need to send the new date and time in the txt file.

So this is the macro i use to send the column E value in the txt file:

If i = 10 Then sOut = sOut & DELIMITER & Left(.Offset(0, 4).Text & String(74, PAD), 12) 

Open in new window


How can i add 7hrs on that code transfer.

Thanks again for your help.

Full code:

   Dim objNet As Object
    On Error Resume Next
    Set objNet = CreateObject("WScript.NetWork")
        
        'vFieldArray contains field lengths, in characters, from field 1 to N
        vFieldArray = Array(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
        nFileNum = FreeFile
        Open "C:\Documents and Settings\All Users\Desktop\ord.02" & Format(Now, "yymmddhhmmss") For Output As #nFileNum
        'Open "E:\JDA_TMS_ORDERS\ord.02" & Format(Now, "yymmddhhmmss") For Output As #nFileNum
        
  
        For Each myRecord In Range("A7:A" & Range("A" & Rows.Count).End(xlUp).Row)
            With myRecord
                .Select
                'sOut = "H" & DELIMITER & "A"
             For i = 0 To 20
   sOut = sOut & DELIMITER & Left(.Offset(0, i).Text & String(vFieldArray(i), PAD), vFieldArray(i))
   '-- column C : i=2
   
   'HEADER
If i = 1 Then sOut = sOut & DELIMITER & Left("H" & String(1, PAD), 1)
If i = 2 Then sOut = sOut & DELIMITER & Left("A" & String(1, PAD), 1)
If i = 3 Then sOut = sOut & DELIMITER & Left(.Offset(0, 0).Text & String(3, PAD), 12)            'Order ID
If i = 4 Then sOut = sOut & DELIMITER & Left(Range("E2").Text & String(12, PAD), 6)              'SCHEDULE
If i = 5 Then sOut = sOut & DELIMITER & Left("HOST" & String(18, PAD), 6)
If i = 6 Then sOut = sOut & DELIMITER & Left(.Offset(0, 1).Text & String(24, PAD), 12)           'Origin ID
If i = 7 Then sOut = sOut & DELIMITER & Left(.Offset(0, 2).Text & String(36, PAD), 26)           'Destination ID


If i = 8 Then sOut = sOut & DELIMITER & Left(.Offset(0, 4).Text & String(74, PAD), 12)          'Early Delivery
If i = 9 Then sOut = sOut & DELIMITER & Left(.Offset(0, 4).Text & String(74, PAD), 12)           'Late Delivery


If i = 10 Then sOut = sOut & DELIMITER & Left(.Offset(0, 3).Text & String(62, PAD), 12)          'Early available
If i = 10 Then sOut = sOut & DELIMITER & Left(.Offset(0, 4).Text & String(74, PAD), 12)          'Late available


If i = 11 Then sOut = sOut & DELIMITER & Left(.Offset(0, 5).Text & String(110, PAD), 12)         'Order Group
If i = 12 Then sOut = sOut & DELIMITER & Left(.Offset(0, 6).Text & String(122, PAD), 1)          'IB / OB    B
If i = 13 Then sOut = sOut & DELIMITER & Left("" & String(122, PAD), 34)                         'EMPTY
If i = 14 Then sOut = sOut & DELIMITER & Left("COL" & String(157, PAD), 3)
If i = 15 Then sOut = sOut & DELIMITER & Left("" & String(160, PAD), 13)                         'EMPTY
If i = 16 Then sOut = sOut & DELIMITER & Left(.Offset(0, 12).Text & String(173, PAD), 20)        'Passchar1
If i = 17 Then sOut = sOut & DELIMITER & Left("" & String(193, PAD), 38)                         'EMPTY
If i = 18 Then sOut = sOut & DELIMITER & Left("M" & String(231, PAD), 1)
If i = 19 Then sOut = sOut & DELIMITER & Left("" & String(232, PAD), 199)                        'EMPTY
If i = 20 Then sOut = sOut & DELIMITER & Left(objNet.UserName & String(432, PAD), 75)            'user tag name


     Next i
                Print #nFileNum, Mid(sOut, Len(DELIMITER) + 1)
                sOut = Empty

Open in new window

LVL 11
Wilder1626Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
I'm not sure where it belongs in the code but this should work.

It creates a 'real' date time from the string and adds 7 hours to that and then it reformats the string as required.
Dim strDT As String
Dim dblDT As Double

    strDT = "201204082300"
    
    strDT = Format(strDT, "0000\/00\/00 00:00")
    
    dblDT = DateValue(strDT) + TimeSerial(Hour(strDT) + 7, 0, 0)
    
    strDT = Format(dblDT, "yyyymmddhhnn")

Open in new window


It might actually be worth creating a small function from this code
Function AddHours(varDT As String, Hrs As Integer) As String
Dim strDT As String
Dim dblDT As Double

    strDT = Format(varDT, "0000\/00\/00 00:00")
    
    dblDT = DateValue(strDT) + TimeSerial(Hour(strDT) + Hrs, 0, 0)
    
    AddHours = Format(dblDT, "yyyymmddhhnn")
End Function

Open in new window

0
Wilder1626Author Commented:
Hello

If i have multiple rows of data, will it add 7 hours to all value from column E that could be different?

Cause normally, they will be different.

That is why i wanted to add 7 hours to the date+time from column E
0
NorieAnalyst Assistant Commented:
Sorry I don't think I follow, do you actually have 'real' dates/times in column E?

Are you looking for a formula or something that changes all the values in that column?

It's easy enough to add 7 hours to a date/time in Excel.

For example, assuming E2 has a 'real' date/time:

=E2+TIME(7,0,0)

By the way, that code does work and will add 7 hours to any date/time that's in the format you posted and return the result in the same format.

It could be integrated in your existing code.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Wilder1626Author Commented:
Sorry for the delay

Thanks for your help

It's all good now
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.