Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Number format issue

Posted on 2012-04-08
4
Medium Priority
?
234 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 35

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 35

Accepted Solution

by:
Norie earned 2000 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

783 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