Accounting Month vs. Calendar Month

[JN]

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.

 

[Peo Sjoblom]

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

 

[N Harkawat]

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]

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]

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";38080,"Apr";38108,"May";38143,"Jun";38171,"Jul";38206,"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