days of year

[cyborgt100]

hi guys having problem i trying create a code so depend on what day it is the code change
prefix is L and the next two number is YY year and the 3 last numbers is DDD (001 - 366) but in number format not words like Sun or Mon so in the case than 366 would be Feb 29 or 206 would be 25 of july. i try vlookup table but it did work either

Sub LotNumbers()


Result = MsgBox("Do you need a lot number?", vbYesNo + vbQuestion)
If Result = vbYes Then
Range("J6").Select
Selection.Font.Color = vbGreen
Selection.Font.Bold = True
dates = Format(Now, "dd,mmm")
Range("J6").Value = "L" & Format(Now, "yy")  & Format(Now, "ddd")
MsgBox "lot number complete "
Else:
Range("J6").Clear
MsgBox "You have No to lot number"
End If
End Sub

all help would be create

 

[Rick Rothstein]

You can get the Day Of The Year using the Format function and the "y" type pattern. So, if your date is July 25, 2019 and it is stored in a variable named FutureDate, then...

DayOfYear = Format(FutureDate, "y")

to ensure a 3-digit output (with leading zeros where necessary), use another Format function call...

DayOfYear = Format(Format(FutureDate, "y"), "000")

You can concatenate the Format function directly into the text your are trying to produce without having to assign it to a variable beforehand if you like.

 

[Fluff]

Try

   Range("J6").Value "L" & Format(Date, "yy") & Format(Format(Date, "d"), "000")

 

[cyborgt100]

Try

   Range("J6").Value "L" & Format(Date, "yy") & Format(Format(Date, "d"), "000")

thank you for but this only give the day of the month like 31 not 090 but is what i need

 

[Fluff]

How about

Range("J6").Value = "L" & Format(Date, "yy") & Format(DateDiff("d", "01/01/" & Year(Date), Date), "000")

 

[cyborgt100]

thank you guys this worked but have issuse on leap year

Sub LotNumbers()


Result = MsgBox("Do you need a lot number?", vbYesNo + vbQuestion)
If Result = vbYes Then
Range("J6").Select
Selection.Font.Color = vbGreen
Selection.Font.Bold = True
dates = Format(Now, "dd,mmm")
Range("J6").Value = "L" & Format(Date, "yy") & Format(Format(Date, "y"), "000")
MsgBox "FX selected please print out Export Sheet "
Else:
Range("J6").Clear
MsgBox "You have No to lot number"
End If
End Sub

if normal year then then the above code works but when leap year it is 29th feb it becomes the 060 and then put the rest of the date out by 1 while i need the code to be 366 on the 29th of feb

 

[Rick Rothstein]

thank you guys this worked but have issuse on leap year

Sub LotNumbers()


Result = MsgBox("Do you need a lot number?", vbYesNo + vbQuestion)
If Result = vbYes Then
Range("J6").Select
Selection.Font.Color = vbGreen
Selection.Font.Bold = True
dates = Format(Now, "dd,mmm")
Range("J6").Value = "L" & Format(Date, "yy") & Format(Format(Date, "y"), "000")
MsgBox "FX selected please print out Export Sheet "
Else:
Range("J6").Clear
MsgBox "You have No to lot number"
End If
End Sub

Why do you have the red highlighted line of code (you do not appear to be using the "dates" variable within your

if normal year then then the above code works but when leap year it is 29th feb it becomes the 060 and then put the rest of the date out by 1 while i need the code to be 366 on the 29th of feb

If I understand what you are asking for correctly, replace this line of code...

[table="width: 500"]
[tr]
	[td]Range("J6").Value = "L" & Format(Date, "yy") & Format(Format(Date, "y"), "000")[/td]
[/tr]
[/table]

with this...

[table="width: 500"]
[tr]
	[td]If Month(DateSerial(Year(Date), 2, 29)) = 2 Then
  Range("J6").Value = "L" & Format(Date, "yy") & 366
Else
  Range("J6").Value = "L" & Format(Date, "yy") & Format(Format(Date, "y"), "000")
End If[/td]
[/tr]
[/table]

 

[cyborgt100]

hi Rick the red line code was excess code after trying different methods throughout the day it now been deleted, you code seem to ge an error when i run the complier wrong number of arguments
thank you for your response

 

[Rick Rothstein]

...your code seem to ge an error when i run the complier wrong number of arguments

On which line of code is that happening for you?

Can you copy/paste the exact line of code that is happening on in a reply to this message?

Just so you know, I cannot repeat this error when I test my code.

 

[cyborgt100]

On which line of code is that happening for you?

Can you copy/paste the exact line of code that is happening on in a reply to this message?

Just so you know, I cannot repeat this error when I test my code.

If Month(DateSerial(year(Date), 2, 29)) = 2 Then