macro 2

[SharonMee]

Hello everyone:

Hope you can help me with this macro:

In cell A3 I want to put "for date between 01/06/2004 and 30/06/2004"

I want excel to insert the date based on the date on the computer. If the date on the computer is July..., it should insert the first day of the previous month (being June) "and" the last day of the previous month.

Is it possible to do this with a macro?

Thanks for helping.

SharonMee

 

[SkipVought]

Sharon,

How can A3 have date between 01/06/2004 and 30/06/2004

WHICH DATE between the first and the last of the month?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[Fherrera]

Hi Sharon,

How's this work? You say by macro... I assume you mean you have to run this macro, ie. either through a command button or what not.

It isn't the prettiest piece of code... I couldn't find any firstdayofmonth type functions so I had to hack something up...

Give it a try.

Sub dateEnter()
    
    Dim result As String
    Dim lastMonth As Date
    Dim firstDayLastMonth As Date
    Dim lastDayLastMonth As Date
    
    result = "for date between "
    
    If Format(Date, "mmmm") = "July" Then
        ' get date of todays date minus 1 month
        lastMonth = DateAdd("m", -1, Date)
        ' subtract total days of month plus 1 from date
        firstDayLastMonth = DateAdd("d", -Day(lastMonth) + 1, lastMonth)
            
        result = result & Format(firstDayLastMonth, "dd/mm/yyyy") & " to "
        ' subtract total days of month from current date (getting last day of last month)
        lastDayLastMonth = DateAdd("d", -Day(Date), Date)
    
        result = result & Format(lastDayLastMonth, "dd/mm/yyyy")
    
        [A3].Value = result
    End If
End Sub

 

[SkipVought]

Here's a simple spreadsheet solution...
[tt]
="Date between "&TEXT(DATE(YEAR(TODAY()),MONTH(TODAY()),1),"dd/mm/yyyy")&" and "&TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1,"dd/mm/yyyy")
[/tt]


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[SharonMee]

Thanks all,

Hi Skip,

I want the date to be the previous month. The worksheet function gave me:

Date between 01/07/2004 and 31/07/2004

I want it to be:

Date between 01/06/2004 and 30/06/2004

How do I modify the function?

Thanks a lot.

 

[SkipVought]

[tt]
="Date between "&TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1),"dd/mm/yyyy")&" and "&TEXT(DATE(YEAR(TODAY()),MONTH(TODAY()),1)-1,"dd/mm/yyyy")
[/tt]


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[PHV]

Simply like this:
="Date between "&TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1),"dd/mm/yyyy")&" and "&TEXT(DATE(YEAR(TODAY()),MONTH(TODAY()),1)-1,"dd/mm/yyyy")
Works even if in january.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SharonMee]

Thanks so much!!!