Dates in excel

  • Thread starter Thread starter trg103
  • Start date Start date
T

trg103

Okay, here's the problem...

I am creating a spreadsheet that sorts out video rentals. When a video
is rented and the number of nights rental is input, the date that the
film is due for return is put into a cell using the following formula:

=NOW()+NO_OF_NIGHTS

n.b. NO_OF_NIGHTS refers to the cell containing number of nights

Now, the problem with this is that when you open the spradsheet the
next day, the date for due return has been updated by one day. How do i
get my speadsheet to not update the date of return???

Please help :-)
 
NOW() is a volatile Function(as you have noticed).

There is no way to prevent it from updating unless you use VBA code.

If you can stand one more cell and one more operation try entering the Date
hard-coded.

e.g. select B1 then hit CRTL + ;(semi-colon) to enter the date. This date
won't change tomorrow.

select C1 and enter =B1 + NO_OF_NIGHTS

If you want to automate it copy/paste this code into the worksheet.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col A
On Error GoTo enditall
If Target.Cells.Column = 1 Then
n = Target.Row
If Range("A" & n).Value <> "" Then
Range("B" & n).Value = Date
Range("C" & n).Value = Date + Range("A" & n)
End If
End If
enditall:
End Sub

Right-click on the sheet tab and select "View Code" and paste the code in.

Now when you input a number into column A(days), column B(day rented) will
have today's date entered and column C(return date) will have the return date
entered.

The Target.Cells.Column = 1 indicates column A. B would be 2, C would be 3
etc. Adjust this and the "A", "B" and "C" to suit your layout.

Gord Dibben XL2002
 
One way is to just enter the date & time directly:

These work for me in my USA keyboard:

ctrl-semicolon will put the date and control-colon (ctrl-shift-semicolon) will
put the time.

(do you really need the time, too?)

if yes,
Ctrl-semicolon spacebar ctrl-colon

if no, then just:
ctrl-semicolon
 
Back
Top