Accounting Month vs. Calendar Month
 

Hi,

I want to know if I can get Excel to show the Accounting Month instead of
calendar month by providing both start and end date of the Accounting Month.

This is the problem I have:
I have a date, for example, April 2, 2004. This is considered April, but in
fact, it is March per the Accounting Month. How can I make Excel to show
March instead of April? I have hundreds of lines and don't want to keep
looking at the 2004 calendar to find out what Accounting Month it belongs to.

Please help.

If it is always 1 month difference use help cells with a formula like

=DATE(YEAR(A1),MONTH(A1)-1,DAY(A1))


Regards,

Peo Sjoblom

"JN" wrote:

Hi,

I want to know if I can get Excel to show the Accounting Month instead of
calendar month by providing both start and end date of the Accounting Month.

This is the problem I have:
I have a date, for example, April 2, 2004. This is considered April, but in
fact, it is March per the Accounting Month. How can I make Excel to show
March instead of April? I have hundreds of lines and don't want to keep
looking at the 2004 calendar to find out what Accounting Month it belongs to.

Please help.

How does one determine what is Acct month. Is it that any month that does
not begin on a Monday is considered previous month for that week or what?


"JN" wrote in message
...
Hi,

I want to know if I can get Excel to show the Accounting Month instead of
calendar month by providing both start and end date of the Accounting
Month.

This is the problem I have:
I have a date, for example, April 2, 2004. This is considered April, but
in
fact, it is March per the Accounting Month. How can I make Excel to show
March instead of April? I have hundreds of lines and don't want to keep
looking at the 2004 calendar to find out what Accounting Month it belongs
to.

Please help.

Thanks, Peo.
But the tricky part is that it isn't always 1 month behind. It can be a
couple days difference, or couple days ahead. For example, 2/28/04 is
considered March per Accounting Calendar I have. Any suggestions? Thanks.

This is a partial Accounting Calendar I have:

12/27/03-1/30/04 = Jan Accounting Month
1/31/04-2/27/04 = Feb Accounting Month
2/28/04 - 4/2/04 = March Accounting Month
4/3/04 - 4/30/04 = April Accounting Month

"Peo Sjoblom" wrote:

If it is always 1 month difference use help cells with a formula like

=DATE(YEAR(A1),MONTH(A1)-1,DAY(A1))


Regards,

Peo Sjoblom

"JN" wrote:

Hi,

I want to know if I can get Excel to show the Accounting Month instead of
calendar month by providing both start and end date of the Accounting Month.

This is the problem I have:
I have a date, for example, April 2, 2004. This is considered April, but in
fact, it is March per the Accounting Month. How can I make Excel to show
March instead of April? I have hundreds of lines and don't want to keep
looking at the 2004 calendar to find out what Accounting Month it belongs to.

Please help.

It seems that the between thye boundary dates in your example there are 35
respectively 28 dates every other month, if so you can create a list like
this for 2004

=VLOOKUP(A1,{37982,"Jan";38017,"Feb";38045,"Mar";3 8080,"Apr";38108,"May";38143,"Jun";38171,"Jul";382 06,"Aug";38234,"Sep";38269,"Oct";38297,"Nov";38332 ,"Dec"},2)

where the numbers are the serial numbers for the dates, if you put a date in
a cell and the format it as general y0ou will get the serial number, so the
dates/numbers are the first dates in your example 38017 = 12/15/03

easier would be to create a list of all the frist dates in the boundaries like

12/15/03 Jan
01/31/04 Feb
02/28/04 Mar

and so on in 12X2 cells, then use

=VLOOKUP(A1,F2:G13,2)

where F2:G13 hold the data


Regards,

Peo Sjoblom



"JN" wrote:

Thanks, Peo.
But the tricky part is that it isn't always 1 month behind. It can be a
couple days difference, or couple days ahead. For example, 2/28/04 is
considered March per Accounting Calendar I have. Any suggestions? Thanks.

This is a partial Accounting Calendar I have:

12/27/03-1/30/04 = Jan Accounting Month
1/31/04-2/27/04 = Feb Accounting Month
2/28/04 - 4/2/04 = March Accounting Month
4/3/04 - 4/30/04 = April Accounting Month

"Peo Sjoblom" wrote:

If it is always 1 month difference use help cells with a formula like

=DATE(YEAR(A1),MONTH(A1)-1,DAY(A1))


Regards,

Peo Sjoblom

"JN" wrote:

Hi,

I want to know if I can get Excel to show the Accounting Month instead of
calendar month by providing both start and end date of the Accounting Month.

This is the problem I have:
I have a date, for example, April 2, 2004. This is considered April, but in
fact, it is March per the Accounting Month. How can I make Excel to show
March instead of April? I have hundreds of lines and don't want to keep
looking at the 2004 calendar to find out what Accounting Month it belongs to.

Please help.