Solved

Number format issue

Posted on 2012-04-08
4
225 Views
Last Modified: 2012-04-21
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

0
Comment
Question by:Wilder1626
  • 2
  • 2
4 Comments
 
LVL 33

Expert Comment

by:Norie
ID: 37822007
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
 
LVL 11

Author Comment

by:Wilder1626
ID: 37823040
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
 
LVL 33

Accepted Solution

by:
Norie earned 500 total points
ID: 37823072
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
 
LVL 11

Author Closing Comment

by:Wilder1626
ID: 37875953
Sorry for the delay

Thanks for your help

It's all good now
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now