Generate Batch Numbers

**mfjanoo** · 09-26-2012, 03:45 AM

I have attached a sample spreadsheet whereby I need to generate weekly production batch numbers.
My week starts on a Monday and ends of on a Friday.

I currently have a formula to calculate this but it does work the way I want it. The formula must not take the weekends into consideration which is currently being done.

Any help regarding is greatly appreciated.

Thanks

Explanation:
Sample : Monday - Friday (equals A - E)
Date: Will depend in which week it falls in (Week - Week 5)
Month = ( 1 - 12)
Year = it manually inserted

**Marcol** · 09-26-2012, 05:24 AM

See if this sample worksheet helps you a bit further on your way.

Select the required W/E date from the drop-down in U1.

**mfjanoo** · 09-26-2012, 06:29 AM

Hi

I have tested your suggestion and works well. However when I select week ending 7 September 2012 the week changes to week 2 instead of week 1. How do I fix that? Thanks for your help.

**Marcol** · 09-26-2012, 07:28 AM

Hold this post, I now see what you are after.
I'll post an amended workbook shortly.

**Marcol** · 09-26-2012, 08:06 AM

Try this amended workbook

See the changes to the lookup sheet and the dynamic named ranges.

**mfjanoo** · 09-26-2012, 01:21 PM

Hi

It works like a dream. YOU GOOD !!!! Thanks for all your help.

I understand the lookups you created, but I am not sure on the batch numbers. If you can please explain that bit to me.

Once thanks a million for all your HELP !!!

**mfjanoo** · 09-26-2012, 03:51 PM

Hi

Just one more query. When I capture the month of October which has 31 days and week end on Wednesday how I capture the 1 & 2 November 2012 as its for one working week.

Essentially I want the spreadsheet to reflect 29 - 31 October and 1 & 2 November 2012 as one working week.

Thanks