lollipopsicle76
New Member
- Joined
- Dec 9, 2024
- Messages
- 2
- Office Version
- 365
- Platform
- Windows
Hi All,
A bit of background - i am creating a spreadsheet to create barcodes which are to assist with scanning our drivers daily worksheets. The barcodes are printed on label sheets which are 33 labels to a page, so i need 33 labels per driver.
I have set up my formulas to get the information i need, but i have a issue with the "date" format dropping the leading zero. The problem i have with making it a date format is that there is no 32nd or 33rd of the month.
Please have a look at the tab named Barcode, i require column F to be in the format DD/MM/YYYY from the 1st to the 33rd label, it then restarts for the next driver entry.
A bit of background - i am creating a spreadsheet to create barcodes which are to assist with scanning our drivers daily worksheets. The barcodes are printed on label sheets which are 33 labels to a page, so i need 33 labels per driver.
I have set up my formulas to get the information i need, but i have a issue with the "date" format dropping the leading zero. The problem i have with making it a date format is that there is no 32nd or 33rd of the month.
Please have a look at the tab named Barcode, i require column F to be in the format DD/MM/YYYY from the 1st to the 33rd label, it then restarts for the next driver entry.
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| G1 | G1 | ='Driver Data'!I2 |
| A3:A38 | A3 | ="*DW-" |
| B3:B38 | B3 | =IF(B2="",'Driver Data'!$G$2,IF(C3="","",IF(C3=1,INDEX('Driver Data'!G:G,MATCH(Barcode!B2,'Driver Data'!G:G,0)+1,1),Barcode!B2))) |
| D3 | D3 | =MONTH(G1) |
| E3 | E3 | =YEAR(G1) |
| F3:F38 | F3 | =C3&"/"&D3&"/"&E3 |
| G3:G38 | G3 | =A3&B3&"-"&C3&"/"&D3&"/"&E3&"*" |
| C4:C34 | C4 | =IF(C3+1=33,1,C3+1) |
| D4:E38 | D4 | =D3 |
| C35:C38 | C35 | =IF(C34+1=34,1,C34+1) |
| I3:I38 | I3 | ="DW "&VLOOKUP(B3,'Driver Data'!G:H,2,FALSE)&" "&Barcode!F3 |
