Solved

Number format issue

Posted on 2012-04-08
4
228 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 34

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 34

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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Suggested Solutions

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

738 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