Welcome to the Excel Forum

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed.

Excel Help Forum » Microsoft Office Application Help - Excel Help forum » Excel General » Excel - New Users » [SOLVED] Batching According to Dates

Please Register to Remove these Ads

Please Register to Remove these Ads



Reply
  #1  
Old 06-29-2009, 04:50 AM
georgeanaprop georgeanaprop is offline
Registered User
  
Join Date: 26 Jun 2009
Location: South Africa
MS Office Version:Excel 2003
Posts: 58
georgeanaprop is becoming part of the community
Batching According to Dates

Please Register to Remove these Ads

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
Last edited by georgeanaprop; 06-29-2009 at 09:37 AM.
Reply With Quote
  #2  
Old 06-29-2009, 05:01 AM
sweep's Avatar
sweep sweep is offline
Forum Guru
  
Join Date: 03 Apr 2007
Location: Skelmersdale UK, probably.
MS Office Version:XL2003 / 2007
Posts: 1,725
sweep is very confident of their ability sweep is very confident of their ability sweep is very confident of their ability sweep is very confident of their ability
Re: Batching According to Dates
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
__________________
This week, I will be mostly overcomplicating things
Reply With Quote
  #3  
Old 06-29-2009, 05:21 AM
georgeanaprop georgeanaprop is offline
Registered User
  
Join Date: 26 Jun 2009
Location: South Africa
MS Office Version:Excel 2003
Posts: 58
georgeanaprop is becoming part of the community
Re: Batching According to Dates
Yes! That's exactly what I was looking for!

Thank You
Last edited by georgeanaprop; 06-29-2009 at 09:38 AM.
Reply With Quote
  #4  
Old 06-29-2009, 09:45 AM
georgeanaprop georgeanaprop is offline
Registered User
  
Join Date: 26 Jun 2009
Location: South Africa
MS Office Version:Excel 2003
Posts: 58
georgeanaprop is becoming part of the community
Re: Batching According to Dates
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!
Reply With Quote
  #5  
Old 06-30-2009, 04:19 AM
sweep's Avatar
sweep sweep is offline
Forum Guru
  
Join Date: 03 Apr 2007
Location: Skelmersdale UK, probably.
MS Office Version:XL2003 / 2007
Posts: 1,725
sweep is very confident of their ability sweep is very confident of their ability sweep is very confident of their ability sweep is very confident of their ability
Re: Batching According to Dates
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))))
__________________
This week, I will be mostly overcomplicating things
Reply With Quote
  #6  
Old 06-30-2009, 09:09 AM
georgeanaprop georgeanaprop is offline
Registered User
  
Join Date: 26 Jun 2009
Location: South Africa
MS Office Version:Excel 2003
Posts: 58
georgeanaprop is becoming part of the community
Re: Batching According to Dates
Thank You once again.
Reply With Quote


Reply

Bookmarks

« Previous Thread | Next Thread »

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off
Forum Jump

Contact Us - ExcelTip.com - Archive - Top