NumberFormat based on Date

jbrouse

Active Member
Joined
Apr 28, 2005
Messages
329
Can someone please assist me in writing a For/If statement that will look at each cell in every other row in range K10:AV48, and format it based on the following conditions:

If the date in each cell is the current year, format it as m/d, but if the date is next year or beyond, format the number as m/yy.

Any assistance would be appreciated. Thanks!
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

MichaelRo

Well-known Member
Joined
Jun 7, 2004
Messages
549
Try This:

Code:
Sub FormatDate()

Dim C As Range
Dim irow As Long
Dim icol As Long

For irow = 10 To 48 Step 2
    For icol = 11 To 48
        Set C = Cells(irow, icol)
            
        If Year(C) = Year(Date) Then
            C.NumberFormat = "M/D"
        Else
            C.NumberFormat = "M/YY"
        End If
    Next icol
Next irow

End Sub

Whoops... :LOL:
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
63,211
Office Version
  1. 365
Platform
  1. Windows
Michael,

Since they want every other row, I think you meant to say:

For irow = 10 To 48 Step 2

instead of

For irow = 10 To 48 Step 1
 

Oorang

Well-known Member
Joined
Mar 4, 2005
Messages
2,071
No, step 1 would hit every one.
Code:
Sub ExampleCode()
Dim MyRng As Range
Dim MyCll As Object
Set MyRng = Range("K10:AV48")
For Each MyCll In MyRng
Application.StatusBar = MyCll.Address & Chr(45) & Year(CDate(MyCll.Value))
'Check to see to if row is even. (If you want to check if odd, change 2 to 1.)
    If MyCll.Row Mod 2 = 0 Then
        If IsDate(MyCll.Value) Then
            If Year(CDate(MyCll.Value)) < 2006 Then
                MyCll.NumberFormat = "m/d"
                Else
                MyCll.NumberFormat = "m/yy"
                End If
            MyCll.Value = CDate(MyCll.Value)
        End If
    End If
Next MyCll
Application.StatusBar = False
End Sub
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,436
Messages
5,831,614
Members
430,077
Latest member
CoulterM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top