Custom formatting assistance

Topic

New Reply

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.

Reply | Quote

Replies

Hi

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

zeddy

Reply | Quote

Wow, that was short and sweet!

Thank you!

Reply | Quote

Hi musical1

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

zeddy

Reply | Quote

Hi Rory

A stunning formula!
Excellant.

zeddy

Reply | Quote

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″))

Reply | Quote

Beautiful! While I find Zeddy’s formula easier to addapt, I SO appreciate the date format approach… gives me future ideas.

Reply | Quote

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. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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!

Reply | Quote

Musical,

Glad to be of assistance. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote