CODEWITHSUNDEEP
excelvba
whytheq
whytheq

Reputation: 35557

How to avoid this strange date coercion

I have this formula in C4: 

=TEXT(EOMONTH(NOW(),-1),"MMM-yy")

The date being displayed is Dec-16 i.e. December 2016

When I then run this vba:

Sub makeDateValues()
  With Range("C4")
      .Value = .Value
  End With
End Sub

The date becomes 16/12/2017.

How do I safely amend the above so that the date stays December-2016 ?

Upvotes: 0

Views: 65

Answers (4)

whytheq
whytheq

Reputation: 35557

I amended the formula to the following:

=TEXT(EOMONTH(NOW(),-1),"MMMM-yyyy")

All ok now.

Upvotes: 0

Limak
Limak

Reputation: 1521

Yo can remove TEXT() from formula to:

=EOMONTH(NOW(),-1)

and just use cell formatting from main tools ribbon to diplay this number as you like.

pic2

pic2

Upvotes: 1

Wolfie
Wolfie

Reputation: 30047

2 options:

Option 1

You can just leave it as a date and, inside your With block, add the line

.numberformat = "MMM-yy"

Option 2

Create the date within VBA in the first place

Range("C4") = Format(Now(), "MMM-yy")

Upvotes: 1

Shai Rado
Shai Rado

Reputation: 33672

Try like this :

Sub makeDateValues()

With Range("C4")
   .Value = Format(WorksheetFunction.EoMonth(Date, -1), "MMM-YYYY")
End With

End Sub

Upvotes: 1

Related Questions