Subtract only Month form the 'Date format' and keep format "mm" using VBA code

[valery-lng]

Dear VB experts,

Please help me with simple task.
I need to subtract one month from the Date formatted as "mm" and result to be kept with same format as "mm"
See code I've created.

Thanks in advance.

 

[valery-lng]

Option Explicit

Sub ForNight_1st_DayOfMonth_ChangeMonth()
    Dim myTime, myMonth As String
    Dim curTime As Date
    Dim curDay As Long
    curTime = Now
    curDay = Day(curTime)
    myTime = Format(Time, "hh:mm")
    myMonth = Format(Date, "mm")
    Debug.Print myMonth ' format "mm"
   
    If curDay = 8 And myTime <= "06:30" Then  ' For night 1st Day of Month
         
            myMonth = Format(Date, "mm") - 1 ' become format "m"
            Debug.Print myMonth

    Else
      Debug.Print myMonth

    End If

   
End Sub

 

[NARAYANK991]

Hi ,

Replace your current line of code :

myMonth = Format(Date, "mm") - 1

by the following :

myMonth = Format(currdt - Day(currdt), "mm")

Narayan

 

[valery-lng]

Thank a lot,

Option Explicit

Sub ForNight_1st_DayOfMonth_ChangeMonth()
    Dim myTime, myMonth As String
    Dim curTime As Date
    Dim curDay, curDate As Long  ' curDate Declared as Long
    curTime = Now
    curDay = Day(curTime)
    curDate = Date          'Format Date is General with value "42529" as Long
    myTime = Format(Time, "hh:mm")
    myMonth = Format(Date, "mm")
    Debug.Print myMonth ' format "mm"
   
    If curDay = 8 And myTime >= "18:30" Then  ' For night 1st Day of Month
         
            myMonth = Format(curDate - Day(curDate), "mm") 'that keeps format "mm"
            Debug.Print myMonth   ' format "mm"

    Else
      Debug.Print myMonth

    End If

End Sub

Simply to resolved
My Code is now works well

Valery

 

[valery-lng]

Dear Narayan,

I intersting,
For 1st January 2017 "01-01-2017",
If I use myDate = Format(Date -1, "dd-mm-yyyy") 'declared as String
Do I get result "31-12-2016"?
And for myDayMonth = Format(Date -1, "dd-mm"), do i get "31-12"?

Could you please clarify it or may be you have other solution.

Best regards,

 

[NARAYANK991]

Dear Narayan,

I intersting,
For 1st January 2017 "01-01-2017",
If I use myDate = Format(Date -1, "dd-mm-yyyy") 'declared as String
Do I get result "31-12-2016"?
And for myDayMonth = Format(Date -1, "dd-mm"), do i get "31-12"?

Could you please clarify it or may be you have other solution.

Best regards,

Hi ,

The general formula is :

Date - Day(Date)

will result in a date in the previous month.

For example , suppose today is June 8 i.e. 08 - 06 - 2016 , assuming your date format is dd - mm - yyyy. If you subtract 8 from this date , the result will be May 31 i.e. 31 - 05 - 2016.

In the special cases where the date is the first of a month , subtracting 1 from the date will result in the last day of the previous month.

Hence , what you have observed regarding January 1 , will be observed in the case of February 1 , March 1 , April 1 ,...

Narayan

 

[valery-lng]

Thanks so much,

That's helped me a lot!

Valery