Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Pank
 
Posts: n/a
Default Formula for current month minus one = Quarter number in a macro.

I have a macro that does several things. The last step should be to save the
file to a network drive with a name and quarter number appended to it.

The file save command I have got is: -

ActiveWorkbook.SaveAs Filename:= _
"Y:\Skip Register\Quarterly Charging Period ?", FileFormat:=xlExcel9795, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False,
CreateBackup:=False

The ? in the file name should be substituted by the formula as described
below.

The macro is to be run in the month following a quarter (i.e. run macro in
April to get Quarter number to 1, run macro in July to get Quarter number to
2, run macro in October to get Quarter number to 3, run macro in January to
get Quarter number to 4).

How can enter effectively enter a formula in a macro which is along the
lines of ((current month €“ 1)/3)). If current month €“ 1 = 0 then set current
month to 12 and undertake the calculation again.

Any help offer is most appreciated.

Thank You

Pank

Lastly, if my version of Excel is 2000, should I substitute 2000 in place of
9795 in the file format above?

  #2   Report Post  
Harald Staff
 
Posts: n/a
Default

Hi Pank

Private Function MyQ() As Long
MyQ = 1 + Int(Month(DateSerial(Year(Date), Month(Date) - 1, 1)) / 4)
End Function

Sub test()
MsgBox MyQ
End Sub

HTH. Best wishes Harald

"Pank" skrev i melding
...
I have a macro that does several things. The last step should be to save

the
file to a network drive with a name and quarter number appended to it.

The file save command I have got is: -

ActiveWorkbook.SaveAs Filename:= _
"Y:\Skip Register\Quarterly Charging Period ?",

FileFormat:=xlExcel9795, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False,
CreateBackup:=False

The ? in the file name should be substituted by the formula as described
below.

The macro is to be run in the month following a quarter (i.e. run macro in
April to get Quarter number to 1, run macro in July to get Quarter number

to
2, run macro in October to get Quarter number to 3, run macro in January

to
get Quarter number to 4).

How can enter effectively enter a formula in a macro which is along the
lines of ((current month - 1)/3)). If current month - 1 = 0 then set

current
month to 12 and undertake the calculation again.

Any help offer is most appreciated.

Thank You

Pank

Lastly, if my version of Excel is 2000, should I substitute 2000 in place

of
9795 in the file format above?



  #3   Report Post  
Bob Phillips
 
Posts: n/a
Default

Private Function QuarterNum(Optional myDate) As Long
If IsMissing(myDate) Then myDate = Date
QuarterNum = Int((Month(DateSerial(Year(myDate), Month(myDate) - 3, 1)) + 2)
/ 3)
Debug.Print QuarterNum, Format(myDate, "dd mmm yyyy")
End Function

Sub test()
QuarterNum DateValue("12/01/2005")
QuarterNum DateValue("12/02/2005")
QuarterNum DateValue("12/03/2005")
QuarterNum DateValue("12/04/2005")
QuarterNum DateValue("12/05/2005")
QuarterNum DateValue("12/06/2005")
QuarterNum DateValue("12/07/2005")
QuarterNum DateValue("12/10/2005")
End Sub


For the fileformat, omit that property, it will default to the format of the
version of Excel being used.

--
HTH

Bob Phillips

"Pank" wrote in message
...
I have a macro that does several things. The last step should be to save

the
file to a network drive with a name and quarter number appended to it.

The file save command I have got is: -

ActiveWorkbook.SaveAs Filename:= _
"Y:\Skip Register\Quarterly Charging Period ?",

FileFormat:=xlExcel9795, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False,
CreateBackup:=False

The ? in the file name should be substituted by the formula as described
below.

The macro is to be run in the month following a quarter (i.e. run macro in
April to get Quarter number to 1, run macro in July to get Quarter number

to
2, run macro in October to get Quarter number to 3, run macro in January

to
get Quarter number to 4).

How can enter effectively enter a formula in a macro which is along the
lines of ((current month - 1)/3)). If current month - 1 = 0 then set

current
month to 12 and undertake the calculation again.

Any help offer is most appreciated.

Thank You

Pank

Lastly, if my version of Excel is 2000, should I substitute 2000 in place

of
9795 in the file format above?



  #4   Report Post  
Pank
 
Posts: n/a
Default

Harald, Bob,

Firstly many thanks for your prompt responses.

I have used Harald's solution (nothing personal Bob) and I need to know how
to get the symbolic MyQ into the file save.

I have used "Y:\Skip Register\Quarterly Charging Period &MyQ" and it saves
it as "Y:\Skip Register\Quarterly Charging Period &MyQ". I then replaced it
as "Y:\Skip Register\Quarterly Charging Period MyQ" and it saved it as
"Y:\Skip Register\Quarterly Charging Period MyQ"

Thanks

Pank

"Bob Phillips" wrote:

Private Function QuarterNum(Optional myDate) As Long
If IsMissing(myDate) Then myDate = Date
QuarterNum = Int((Month(DateSerial(Year(myDate), Month(myDate) - 3, 1)) + 2)
/ 3)
Debug.Print QuarterNum, Format(myDate, "dd mmm yyyy")
End Function

Sub test()
QuarterNum DateValue("12/01/2005")
QuarterNum DateValue("12/02/2005")
QuarterNum DateValue("12/03/2005")
QuarterNum DateValue("12/04/2005")
QuarterNum DateValue("12/05/2005")
QuarterNum DateValue("12/06/2005")
QuarterNum DateValue("12/07/2005")
QuarterNum DateValue("12/10/2005")
End Sub


For the fileformat, omit that property, it will default to the format of the
version of Excel being used.

--
HTH

Bob Phillips

"Pank" wrote in message
...
I have a macro that does several things. The last step should be to save

the
file to a network drive with a name and quarter number appended to it.

The file save command I have got is: -

ActiveWorkbook.SaveAs Filename:= _
"Y:\Skip Register\Quarterly Charging Period ?",

FileFormat:=xlExcel9795, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False,
CreateBackup:=False

The ? in the file name should be substituted by the formula as described
below.

The macro is to be run in the month following a quarter (i.e. run macro in
April to get Quarter number to 1, run macro in July to get Quarter number

to
2, run macro in October to get Quarter number to 3, run macro in January

to
get Quarter number to 4).

How can enter effectively enter a formula in a macro which is along the
lines of ((current month - 1)/3)). If current month - 1 = 0 then set

current
month to 12 and undertake the calculation again.

Any help offer is most appreciated.

Thank You

Pank

Lastly, if my version of Excel is 2000, should I substitute 2000 in place

of
9795 in the file format above?




  #5   Report Post  
Harald Staff
 
Posts: n/a
Default

Hi Pank

Everything inside quotes mean "literally". So end them before a variable:

"Y:\Skip Register\Quarterly Charging Period" & MyQ

HTH. Best wishes Harald

"Pank" skrev i melding
...
Harald, Bob,

Firstly many thanks for your prompt responses.

I have used Harald's solution (nothing personal Bob) and I need to know

how
to get the symbolic MyQ into the file save.

I have used "Y:\Skip Register\Quarterly Charging Period &MyQ" and it saves
it as "Y:\Skip Register\Quarterly Charging Period &MyQ". I then replaced

it
as "Y:\Skip Register\Quarterly Charging Period MyQ" and it saved it as
"Y:\Skip Register\Quarterly Charging Period MyQ"





  #6   Report Post  
Pank
 
Posts: n/a
Default

Harald,

Sorry to be a pain in the bo**om.

The end part of the macro is as follows:-

test
ActiveWorkbook.SaveAs Filename:= _
"Y:\Skip Register\Quarterly Charging Urban Vision Period" & MyQ,
Password:="", _
WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False

Unfortunately, it does not insert the numeric represented by MyQ into the
file name.

Additionally, you supplied:-

'MyQ = 1 + Int(Month(DateSerial(Year(Date), Month(Date) - 1, 1)) / 4)

Can you please clarify whether the digit 4 is correct or should it be 3 to
represent quarters (i.e. every 3 months)?

And lastly, if you would be good enough to explain the above it would aid me
greatly.

Any ideas?

Many thanks

Pank



"Harald Staff" wrote:

Hi Pank

Everything inside quotes mean "literally". So end them before a variable:

"Y:\Skip Register\Quarterly Charging Period" & MyQ

HTH. Best wishes Harald

"Pank" skrev i melding
...
Harald, Bob,

Firstly many thanks for your prompt responses.

I have used Harald's solution (nothing personal Bob) and I need to know

how
to get the symbolic MyQ into the file save.

I have used "Y:\Skip Register\Quarterly Charging Period &MyQ" and it saves
it as "Y:\Skip Register\Quarterly Charging Period &MyQ". I then replaced

it
as "Y:\Skip Register\Quarterly Charging Period MyQ" and it saved it as
"Y:\Skip Register\Quarterly Charging Period MyQ"




  #7   Report Post  
Bob Phillips
 
Posts: n/a
Default



"Pank" wrote in message
...
Harald, Bob,


I have used Harald's solution (nothing personal Bob) and I need to know

how
to get the symbolic MyQ into the file save.


I am offended <vbg

They actually give different answers depending upon the interpretation, so
you need to use the one that conforms to your requirement

Date Harald Bob
12 Jan 2005 4 4
12 Feb 2005 1 4
12 Mar 2005 1 4
12 Apr 2005 1 1
12 May 2005 2 1
12 Jun 2005 2 1
12 Jul 2005 2 2
12 Aug 2005 2 2
12 Sep 2005 3 2
12 Oct 2005 3 3
12 Nov 2005 3 3
12 Dec 2005 3 3

I also answered the fileformat bit.



  #8   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

On Tue, 21 Jun 2005 01:53:34 -0700, "Pank"
wrote:

How can enter effectively enter a formula in a macro which is along the
lines of ((current month – 1)/3)). If current month – 1 = 0 then set current
month to 12 and undertake the calculation again.



Qrtr = DatePart("q", DateAdd("m", -1, Date))

Lastly, if my version of Excel is 2000, should I substitute 2000 in place of
9795 in the file format above?


I do not see that as an available choice in XL2002, so I would guess not.



--ron
  #9   Report Post  
Harald Staff
 
Posts: n/a
Default

"Pank" skrev i melding
...
'MyQ = 1 + Int(Month(DateSerial(Year(Date), Month(Date) - 1, 1)) / 4)

Can you please clarify whether the digit 4 is correct or should it be 3 to
represent quarters (i.e. every 3 months)?


Doh ! Really sorry about that. This is more like what I intended:

MyQ = 1 + Int((Month(DateSerial(Year(Date), Month(Date) - 1, 1)) - 1) / 3)

it will return for each month
jan 4
feb 1
mar 1
apr 1
may 2
jun 2
jul 2
aug 3
sep 3
oct 3
nov 4
dec 4
but it may not be what you wanted.

Best wishes Harald


  #10   Report Post  
Dana DeLouis
 
Posts: n/a
Default

April to get Quarter number to 1, run macro in July to get Quarter number
to
2, ...


Just another idea. If you run your macro anytime in July, Aug, or Sep, and
still want the previous quarter, perhaps another option...

Qrtr = 499 Mod (Format(Date, "q") + 4)

HTH
--
Dana DeLouis
Win XP & Office 2003


"Pank" wrote in message
...
I have a macro that does several things. The last step should be to save
the
file to a network drive with a name and quarter number appended to it.

The file save command I have got is: -

ActiveWorkbook.SaveAs Filename:= _
"Y:\Skip Register\Quarterly Charging Period ?",
FileFormat:=xlExcel9795, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False,
CreateBackup:=False

The ? in the file name should be substituted by the formula as described
below.

The macro is to be run in the month following a quarter (i.e. run macro in
April to get Quarter number to 1, run macro in July to get Quarter number
to
2, run macro in October to get Quarter number to 3, run macro in January
to
get Quarter number to 4).

How can enter effectively enter a formula in a macro which is along the
lines of ((current month - 1)/3)). If current month - 1 = 0 then set
current
month to 12 and undertake the calculation again.

Any help offer is most appreciated.

Thank You

Pank

Lastly, if my version of Excel is 2000, should I substitute 2000 in place
of
9795 in the file format above?





  #11   Report Post  
Pank
 
Posts: n/a
Default

Harald,

You must have missed the first part of the post which was:-

The end part of the macro is as follows:-

test
ActiveWorkbook.SaveAs Filename:= _
"Y:\Skip Register\Quarterly Charging Urban Vision Period" & MyQ,
Password:="", _
WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False

Unfortunately, it does not insert the numeric represented by MyQ into the
file name.

ANy Ideas?

Thanks

Pank

"Harald Staff" wrote:

"Pank" skrev i melding
...
'MyQ = 1 + Int(Month(DateSerial(Year(Date), Month(Date) - 1, 1)) / 4)

Can you please clarify whether the digit 4 is correct or should it be 3 to
represent quarters (i.e. every 3 months)?


Doh ! Really sorry about that. This is more like what I intended:

MyQ = 1 + Int((Month(DateSerial(Year(Date), Month(Date) - 1, 1)) - 1) / 3)

it will return for each month
jan 4
feb 1
mar 1
apr 1
may 2
jun 2
jul 2
aug 3
sep 3
oct 3
nov 4
dec 4
but it may not be what you wanted.

Best wishes Harald



  #12   Report Post  
Pank
 
Posts: n/a
Default

Thank you to Harald, Bob, Ron and Dana for the solutions you supplied.

Works a treat.

Regards

Pank

"Dana DeLouis" wrote:

April to get Quarter number to 1, run macro in July to get Quarter number
to
2, ...


Just another idea. If you run your macro anytime in July, Aug, or Sep, and
still want the previous quarter, perhaps another option...

Qrtr = 499 Mod (Format(Date, "q") + 4)

HTH
--
Dana DeLouis
Win XP & Office 2003


"Pank" wrote in message
...
I have a macro that does several things. The last step should be to save
the
file to a network drive with a name and quarter number appended to it.

The file save command I have got is: -

ActiveWorkbook.SaveAs Filename:= _
"Y:\Skip Register\Quarterly Charging Period ?",
FileFormat:=xlExcel9795, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False,
CreateBackup:=False

The ? in the file name should be substituted by the formula as described
below.

The macro is to be run in the month following a quarter (i.e. run macro in
April to get Quarter number to 1, run macro in July to get Quarter number
to
2, run macro in October to get Quarter number to 3, run macro in January
to
get Quarter number to 4).

How can enter effectively enter a formula in a macro which is along the
lines of ((current month - 1)/3)). If current month - 1 = 0 then set
current
month to 12 and undertake the calculation again.

Any help offer is most appreciated.

Thank You

Pank

Lastly, if my version of Excel is 2000, should I substitute 2000 in place
of
9795 in the file format above?




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Date formula Mike R Excel Discussion (Misc queries) 7 January 20th 05 11:07 PM
formula to have each new invoice automatically have the next numb Jacqui Excel Discussion (Misc queries) 1 January 15th 05 08:12 PM
what formula do i put for column m = column k minus column l in e. jenniss Excel Discussion (Misc queries) 5 January 6th 05 09:18 PM
Help with macro formula and variable Huge project Excel Worksheet Functions 0 December 28th 04 02:27 AM
Formula to convert to month Mel Excel Worksheet Functions 2 December 2nd 04 12:52 AM


All times are GMT +1. The time now is 01:07 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"