LOT number generator - date and static text

[feelkg211]

[FONT=&quot]I have an if statement =IF(D2="","",TODAY()+2) but I wanna change it so if D2 is a nonzero, the cell will produce a lot number with a format of mmddy202P.[/FONT]
[FONT=&quot]202P is a standard ending for the LOT Numbers. I was also hoping that the year will only have one digit (end of the year digit: 2017 =7).[/FONT]
[FONT=&quot]So for example I input something in D2 today (9/26/2017), the LOT number Generated will be 09287202P.[/FONT]
[FONT=&quot]Cell formatting only allows mmddyy.[/FONT]
[FONT=&quot]Any suggestions?[/FONT]

 

[AhoyNC]

There is probably a better way but this should work:

Excel Workbook
	D	E
1	Date	Lot#
2	9/26/2017	09287202P
3	10/2/2017	10047202P
Sheet

 

[istiasztalos]

Hi,

The 2 days should be added before you look at month number, otherwise it will not switch to next mont if used like on Sept 30th...

Apart from the 1 digit year you can do this directly with the TEXT formula (attention the codes depend on your PCs regional settings, these are the English codes...)

TEXT(D2+2,"mmdd")&RIGHT(YEAR(D2+1))&"202P"

 

[feelkg211]

This was really helpful!! Is there a way for it to land only on weekdays?

 

[AhoyNC]

If you add 2 days to Thursday it becomes Sat., so would you want it to move to Monday, and 2 days added to Fri. is Sun. so should that become Tues.?
What if cell D2 has a Sat. or Sun. date then what?

If D2 will not have a Sat. or Sun. date then try this slight change to the formula from istiasztalos.

=TEXT(WORKDAY(D2,2),"mmdd")&RIGHT(YEAR(D2),1)&"202P"

 

[feelkg211]

Well the lot numbers usually land on a Monday, Wednesday, or Friday..

 

[AhoyNC]

If Fri should move to Tues. formula in E2.
If Fri should move to Monday then formula in F2.

Excel Workbook
	D	E	F
1		If Fri should move to Tues.	If Fri should move to Mon
2	Monday, September 4, 2017	09067202P	09067202P
3	Wednesday, September 6, 2017	09087202P	09087202P
4	Friday, September 8, 2017	09127202P	09117202P
Sheet

 

[feelkg211]

Worked!! Thanks