Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
is there a formula to generate a calendar month date rather than . | Excel Worksheet Functions | |||
How to extract month number from month name | Excel Discussion (Misc queries) | |||
Calendar and info for that date | Excel Discussion (Misc queries) | |||
Sm Product a Calendar Month Range? | Excel Worksheet Functions | |||
Convert week number into calendar month? | Excel Worksheet Functions |