Locking Date after using =Now()

I have a cell (A1) which has the formula =“Packing List - ”&Now(). I want the last part of cell A1 to show the date in dd/mm/yy format. It now only shows the date as a number. How do I do this? Using VBA is OK

Thanks Sandy

If you use TODAY() or NOW() functions in a cell in a formula, that formula will update to the current date/time every time you open the workbook or the workbook calculates. That means opening and printing this workbook a week from now will have next week's date on it.

To still avoid VBA, you can do this, but it requires the use of a DROP DOWN in that cell so that you are effectively "selecting right now" from a text box, you still don't have to type in the cell and the value goes into the cell AS TEXT, so it won't update in the future unless you select the cell's drop down again and update the choice.

HOW TO DO IT

1) In an empty cell off to the right somewhere out of the way (AA1 for example), put this formula:

=TODAY()

2) Click on the cell type ThisDate into the Name Box, the box to the left of the formula bar that normally shows the cell you have selected.

3) Now select the cells where you want this date to appear and open the Data > Validation window, use these settings:

Allow: List
Source: =ThisDate

OK

4) Now format those cells with a custom number format of:

"Packing List - "dd/mm/yy


That will do it. Just click on the cell, you will see today's date, click it and your permanent non-changing packing date will be entered.

gsandy,

Posted at the same time as JBeaucaire, so editing my post to remove the non-VBA part. The following is a macro you could use if you wanted to use VBA:

Please Login or Register  to view this content.

The macro will populate cell A1 with the required data and the date won't change as the day changes.

Hope this helps,
~tigeravatar