Batching According to Dates

**georgeanaprop** · 06-29-2009, 05:50 AM

Hi

I'm not very experienced with excel and am just looking for a basic formula to help me out

Basically, we have a system that works on a batch system. The Batch will run from the 21st of the Previous month until the 20th of the Current month

For example: All invoices and payments made from Jan 21st 2009 up to and including February 20th 2009 will be listed in the Period or "Batch" February 2009; or everything between June 21st 2009 up to and including July 20th 2009 will be in the "July 2009" Batch

My question is, if i have a date in Cell B2 (eg 23/03/2009), I would like it to display in an adjacent cell (A2) "April 09" by recognising that it falls between the 21st day (inclusive) of the prior month and the 20th day (also inclusive) of the current month being diplayed in Cell B2.

How is this achieved?

All help is most appreciated

Kind Regards

George

**sweep** · 06-29-2009, 06:01 AM

Hi,

Try this:

=IF(DAY(B2)>=20,VALUE(MONTH(B2)+1&"/"&YEAR(B2)),VALUE(MONTH(B2)&"/"&YEAR(B2)))

You'll need to format the column containing this formula:

Custom > mmmm/yy

**georgeanaprop** · 06-29-2009, 06:21 AM

Yes! That's exactly what I was looking for!

Thank You

**georgeanaprop** · 06-29-2009, 10:45 AM

Just came across a small hitch - when the date is after December 20th, it gives an error "#VALUE!" - most likely from the formula:

=IF(DAY(B2)>=20,VALUE(MONTH(B2)+1&"/"&YEAR(B2)),VALUE(MONTH(B2)&"/"&YEAR(B2)))

not being able to recognise that it has to go one year ahead ie. from 2008 - 2009 because the "YEAR" is still being traced from that in Cell B2

Any ideas on how to adjust it so that it will hop one year forward when it reaches a "January Batch" ie Dec 21st 2008 - Jan 20th 2009?

Thanks!

**sweep** · 06-30-2009, 05:19 AM

Sorry for the oversight

=IF(AND(MONTH(B2)=12,DAY(B2)>=20),VALUE(MONTH(1)&"/"&YEAR(B2)+1),IF(DAY(B2)>=20,VALUE(MONTH(B2)+1&"/"&YEAR(B2)),VALUE(MONTH(B2)&"/"&YEAR(B2))))