Question

Locked

Date format in Macro

By Lance1234 ·
I am a newby in macro and would like to create a Macro in Excel that would note the date today and tomorrow via format mm/dd only, place it in a cell, which will later be copied to become an input as the filter category for a specific column. I was able to come up with a script (below) however, the date that was being used has a format of mm/dd/yyyy, which causes the filter to return 0 column since it did not satisfy the filter criteria. Kindly help me resolve this. Thanks.

Macro Script:

Range("Z3:Z4").Select
Selection.NumberFormat = "mm/dd;@"
Range("Z3").Select
ActiveCell.Value = Int(Now())
Range("Z4").Select
ActiveCell.Value = Int(Now + 1)

Range("A1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=5, Criteria1:=Range("Z3").Value, Operator:=xlOr, _
Criteria2:=Range("Z4").Value

This conversation is currently closed to new comments.

1 total post (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

There is a difference between what is stored in a cell and what you see...

by ThumbsUp2 In reply to Date format in Macro

When you place a number in a cell, what you've placed there remains even though you format it differently. So, when you place Now() into a cell, it will contain a number which represents the full mm/dd/yy hh:mm:ss.

You've also got mismatched parrens in the Now() + 1 value.

In order to match your filter, you'll either have to use Int(Now()) and Int(Now()+1) as criteria or parse out a portion of the contents of cells Z3 and Z4 into variables which which extract the first 5 characters such as Text(Z3,"mm/dd") and Text(Z4,"mm/dd")

Back to Software Forum
1 total post (Page 1 of 1)  

Related Forums