I need a date!

[imported_unknown]

I am writing a library 'program' in excel, and i've hit a snag. when people sign out books, the date is printed. the problem is this date keeps on changing everytime you open up the spreadsheet. is there a way to put a 'now' function, that doesn't change the date each time you load it?

-Zak


p.s. im having trouble with the followting things too:

Avaliable on/off (relates to other fields)
Overdue yes/no (relates to return date)
and color change depending if certain criteria are met.

!!!

p.p.s. Yes, i am a noob at this.

 

[Von Pookie]

Well, for color change, you could probably just use Conditional Formatting. It's under the Format menu.

 

[Barry Katcher]

For the first question, use this macro:

Sub Date()
Range("cell you want date in").Select
ActiveCell.FormulaR1C1 = "=NOW()"
Range("cell you want date in").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End Sub


This enters the NOW() in the cell and then copies it and pastes it as a value back to the same cell.

Working on other questions.

 

[Brian from Maui]

CTRL + : AND CTRL + SHFT + : for day and time

This for cell pattern coloring

http://216.92.17.166/board/viewtopic.php?topic=17587&forum=2

 

[jdavis9]

instead of using the =now()
try running the following.
Select the cell to contain the date.
then run the following macro.

ActiveCell.FormulaR1C1 = "=today()"
ActiveCell.Copy
ActiveCell.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Selection.NumberFormat = "mm/dd/yy"

 

[Barry Katcher]

For the "Available", I assume you mean is the book in the library or checked out. Let's assume that you have two columns, one for check-out date in cells c10 to c30, and one for corresponding check-in dates in cells d10 to d30. If there is one more date in column C than in column D, we can assume the book has been checked out one more time than checked in, hence it is out. Therefore, in any cell you choose, place the following formula:

IF(COUNT(C10:C30)=COUNT(D10:D30),"Available","Still Out")


Re: coloring cells, follow Pookie.

 

[Yogi Anand]

On 2002-10-03 14:50, Barry Katcher wrote:
For the first question, use this macro:

Sub Date()
Range("cell you want date in").Select
ActiveCell.FormulaR1C1 = "=NOW()"
Range("cell you want date in").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End Sub


This enters the NOW() in the cell and then copies it and pastes it as a value back to the same cell.

Working on other questions.

Hi Barry:

Since the OP wants the date entered to be static (and not dynamic), how about simplifying the code to ...

Sub Date() 
Range("cell you want date in").Select 
ActiveCell.value= "=NOW()" 
End Sub

This will alleviate the need to copy the formula first and then pasting its value.

If we want simply the date and not both the date and time, we could could also use

ActiveCell.value="=Date()"


Regards!

Yogi
This message was edited by Yogi Anand on 2002-10-03 15:15

 

[Barry Katcher]

Looks real good to me, Yogi. I just quickly recorded a macro and didn't look at it that closely, only made sure it worked. Yours is much cleaner

 

[imported_unknown]

macro?

oh, and the expression for 'blank' is "" right? bucause i did a formula to see if the book is checked out or not and it went somehting like this:

=(A3:C3="",Yes,No) and it turned up errors.

!!!
-Zak
This message was edited by theoneandonlyzak04 on 2002-10-03 17:43

 

[Brian from Maui]

Besides the date being static, what exactly are you trying to do with color changes? As an example what would be the criteria(s) to have the cell change color?