Date of a movie due back

[MattAndrews]

I'm programming an excel workbook to manage a video store.

Currently I'm creating a sheet that manages invoices. So far, I ente
the movie's code (labelled on it's box) and the customer's member I
(on their card). Upon doing this, the name of the movie and it's cos
per day for rental all appear automatically in the relevant cells usin
lookup from my movie database. a similar things occurs with the looku
of the member's name and surname.

Then I enter the amount of days the customer wishes to rent the movi
for. Now here's the problem.

I want excel to tell me when the movie is due back.

I tried having a single cell displaying the value of =TODAY() an
referencing it using formulas, by adding the amount of days for renta
to the date. unfortunately, when i open the workbook the next day, al
of the dates have increased by one, as the day changes. I want to kee
a consistent record of when a movie is due back because from that
need to calculate costs for an overude movie. How can I do it so tha
the calculated dates don't change?

Also, how can I make the 'number of copies' digit in the movie workboo
(that contains all the movie names and details) decrease by one ever
time it's code is added to the invoice book? I need to do this so I ca
easily check whether a movie is in stock or not.

Thanks in anticipation,

Matt.
age 1

 

[Andy Wiggins]

Don't use TODAY() as it will always give the current date (but you've found
that out).

Input today's date by pressing Ctrl+;

Your formulas should now work.

--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"

 

[Bob Phillips]

Matt,

VBA is probably the easiest way.

Assuming the movie code is in column A, the member id in column B, and the
days in column C, date due in column D, the stock is on sheet 2 with the
code in A, count ijn B, than add this code to the worksheet code module
(right-click on the sheet tab name, select View Code, and then paste the
code)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range

Application.EnableEvents = False
On Error GoTo ws_exit
If Target.Column = 3 Then
Target.Offset(0, 1).Value = Date + Target.Value
Target.Offset(0, 1).NumberFormat = "dddd dd mmm yyyy"
With Worksheets("Sheet2")
Set rng = .Columns("A:A").Find(Target.Offset(0, -2).Value)
If Not rng Is Nothing Then
rng.Offset(0, 1).Value = rng.Offset(0, 1).Value - 1
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Frank Kabel]

Hi

the function TODAY() will be re-calculated everytime you open your
spreadsheet. You have to use some VBA code (as Bob posted) or use a
solution posted by Harlan Grove some days ago (I really liked his idea
as you can use the defined name in any formula on your spreadsheet).
e.g.
IF(A1<>"",__NOW__,"")

HTH
Frank


Here is a copy of Harlan's post:
---------------------------------

Same general idea I had, but I'd do it differently. I'd have the Open
event
add the workbook-level name __NOW__ with the date/time value when the
workbook was first opened by a user (rather than the developer, who
would
need to leave __NOW__ undefined or initialized to #N/A). Then the name
__NOW__ could be used anywhere in any formula in the workbook.


Private Sub Workbook_Open()
On Error GoTo CleanUp
Application.EnableEvents = False

If IsError(Evaluate("__NOW__")) Then _
Me.Names.Add Name:="__NOW__", RefersTo:=Now

CleanUp:
Application.EnableEvents = True
End Sub

---------------------------------

 

[MattAndrews]

I used Bob's solution, worked great!
Only one problem. On the sheet for the movie list, the value of th
current amounts of movies in stock are all messed up, most of them ar
minus figures, even if they only appear once on the list in th
transactions sheet, and had say 3 copies to start with

 

[Bob Phillips]

Probably best to add a check to not let it go negative

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range

Application.EnableEvents = False
On Error GoTo ws_exit
If Target.Column = 3 Then
Target.Offset(0, 1).Value = Date + Target.Value
Target.Offset(0, 1).NumberFormat = "dddd dd mmm yyyy"
With Worksheets("Sheet2")
Set rng = .Columns("A:A").Find(Target.Offset(0, -2).Value)
If Not rng Is Nothing Then
If rng.Offset(0, 1).Value > 0 Then
rng.Offset(0, 1).Value = rng.Offset(0, 1).Value - 1
End If
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[MattAndrews]

Thanks very much for that! it worked great.
but, it revealed a new error. Say I have 'The Matrix' stored as
copies in stock. I can enter it say five times into the transaction
database, and the value for it's stock still shows as 0. How can
modify my lookup to make it so that if there is 0 copies in stock, whe
I enter it's code into the transactions sheet, it displays a 'not i
stock' error? This should be pretty simple but I'm having problem
getting my head round this

 

[Bob Phillips]

Matt,

Version 17/a/XX/2.4<G>

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range

Application.EnableEvents = False
On Error GoTo ws_exit
If Target.Column = 3 Then
Target.Offset(0, 1).Value = Date + Target.Value
Target.Offset(0, 1).NumberFormat = "dddd dd mmm yyyy"
With Worksheets("Sheet2")
Set rng = .Columns("A:A").Find(Target.Offset(0, -2).Value)
If Not rng Is Nothing Then
If rng.Offset(0, 1).Value > 0 Then
rng.Offset(0, 1).Value = rng.Offset(0, 1).Value - 1
Else
Target.Offset(0,2).Value ="No stock copies"
End If
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)