I need a date!

imported_unknown

Active Member
Joined
Jan 13, 2002
Messages
424
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.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
Well, for color change, you could probably just use Conditional Formatting. It's under the Format menu.
 

Barry Katcher

Well-known Member
Joined
Feb 25, 2002
Messages
4,053
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.
 

jdavis9

Active Member
Joined
Mar 8, 2002
Messages
337

ADVERTISEMENT

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

Well-known Member
Joined
Feb 25, 2002
Messages
4,053
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

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454

ADVERTISEMENT

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 ...

Code:
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

Well-known Member
Joined
Feb 25, 2002
Messages
4,053
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

Active Member
Joined
Jan 13, 2002
Messages
424
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

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458
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?
 

Forum statistics

Threads
1,143,917
Messages
5,721,529
Members
422,369
Latest member
redinator

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top