Number To Date Format

[james_halliwell]

Hi All,

looking for some advise, on a barcode on our products it contains the expiery date for that product, i can cut out the information i need from the barcode using LEN And LEFT but the proble i face is its only a number for instance

Bar code
+$$31509303206613NC

Cut down to info needed
1509

the expiery date is September 2011

Is there a way to convert 1509 to say September 2011

Any thoughts on this

Many Thanks

 

[JANR]

Use Format() and Dateserial() functions.

x=1509
format(dateserial(2011,right(x,2),left(x,2)),"mmmm yyyy")

gives

september 2011


JR

 

[james_halliwell]

Use Format() and Dateserial() functions.

x=1509
format(dateserial(2011,right(x,2),left(x,2)),"mmmm yyyy")

gives

september 2011


JR

Sorry the expiry date would be September 2015
15 is the year , 09 is the month

The year could change

many thanks

 

[JANR]

Then change the Dateserial function.

DateSerial(Year,Mounth,Day)

so

Format(Dateserial(20 & Left(x,2), Right(x,2), 1)), "mmmm yyyy")

JR

 

[james_halliwell]

Then change the Dateserial function.

DateSerial(Year,Mounth,Day)

so

Format(Dateserial(20 & Left(x,2), Right(x,2), 1)), "mmmm yyyy")

JR

Absolute legend my fiend, thank you very much will save me loads of time, added to rep, never even heard of Dateserial

Anyone else using this example use below code there was just an extra ) in the above code


Format(DateSerial(20 & Left([ExpiryDateNum],2),Right([ExpiryDateNum],2),1),"mmmm yyyy")

 

[JANR]

Happy to help

JR