Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Edison, New Jersey, USA
    Posts
    215
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Custom formatting assistance

    My spreadsheet contains data that was extracted from BOXI.
    The dates are currently in the following character format: 20110805
    Those numbers represent August 5, 2011.

    My goal is to perform a calculation of the dates: Date A minus Date B

    In their current character format, I was unable to do so. My workaround was to create a 'date conversion' tab, where I dumped the column of unformatted dates, performed a text-to-column function and concatentated the numbers back into the format that I wanted (08/05/2011) prior to performing the calculation.


    This was an OK work-around, as I only had to do it once a month for 3 columns. Now, I have to perform it weekly and I need the smarter - not harder approach.

    In the attachment, rows 6-8 show the data 'as is'. Rows 10-12 show what my goal is.
    Attached Files Attached Files

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi

    See attached file.
    I put some formulas in to give you the results you wanted.

    zeddy
    Attached Files Attached Files

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Edison, New Jersey, USA
    Posts
    215
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Wow, that was short and sweet!

    Thank you!

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    FWIW, you could also use in F7:
    =IF(ISERROR((TEXT(D7,"0000\-00\-00"))-(TEXT(C7,"0000\-00\-00"))),"NO DATA",TEXT(E7,"0000\-00\-00")-TEXT(D7,"0000\-00\-00"))
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi musical1

    You're welcome.
    I assume it works then (or you can adapt as required).

    zeddy

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Rory

    A stunning formula!
    Excellant.

    zeddy

  7. #7
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Edison, New Jersey, USA
    Posts
    215
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Beautiful! While I find Zeddy's formula easier to addapt, I SO appreciate the date format approach... gives me future ideas.
    Last edited by musical1; 2012-02-17 at 12:34.

  8. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Musical,

    Just in case you need another method.

    As I read your OP I assumed that the desired outcome area was just for showing what you wanted the original area transformed into so I took the VBA approach to reformat the existing data {rows 6-8}.
    Code:
    Option Explicit
    
    Sub TextToDate()
    
       Dim lRowCnt   As Long
       Dim lColCnt   As Long
       Dim lCntrR    As Long
       Dim lCntrC    As Long
       Dim zTemp     As String
    
       With Selection
           lRowCnt = .Rows.Count
           lColCnt = .Columns.Count
       End With
       
       ActiveCell.Select
       
       With ActiveCell
       For lCntrR = 0 To lRowCnt - 1
          For lCntrC = 0 To lColCnt - 1
             zTemp = .Offset(lCntrR, lCntrC).Value
             With .Offset(lCntrR, lCntrC)
                 .Value = Mid(zTemp, 5, 2) & "/" & Right(zTemp, 2) & "/" & Left(zTemp, 4)
                 .HorizontalAlignment = xlCenter
             End With
          Next lCntrC
             With .Offset(lCntrR, 3)
                 .FormulaR1C1 = "=if(iserror(RC[-2]-RC[-3])," & Chr(34) & "NO DATA" & Chr(34) & ",RC[-1]-RC[-2])"
                 .HorizontalAlignment = xlCenter
             End With
             With .Offset(lCntrR, 4)
                 .FormulaR1C1 = "=if(iserror(RC[-2]-RC[-4])," & Chr(34) & "NO DATA" & Chr(34) & ",RC[-2]-RC[-4])"
                 .HorizontalAlignment = xlCenter
             End With
       Next lCntrR
       End With  'ActiveCell
       
    End Sub
    The code assumes you'll highlight the dates to be converted and then counts the rows & columns in the range. I assume this will always be 3 cols but an unknown number of columns. The code will convert the dates then place the formulas and center the values. It does not place the boarders but you can see how to add that if necessary. YMMV.
    Attached Images Attached Images
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. #9
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Edison, New Jersey, USA
    Posts
    215
    Thanks
    4
    Thanked 0 Times in 0 Posts
    RG - That was amazing! I love macros, but I'm very weak in that area of EXCEL.
    That was so fun that I was opening my old data sheets and converting them!

  10. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Musical,

    Glad to be of assistance.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •