Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Number format issue

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

704 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