Joining String with function in IF()?

L

Lewis Koh

A B
1 $100 =IF(NOT(ISBLANK(A1)), "Deposited on" + NOW())

Can I join a string like "Deposited on" with a function like above?
wanted to make it auto update on the date when I key in $100.

Why does =IF(NOT(ISBLANK(A1)),NOW()) shows 38563.68864? I can't get th
date out.Pls hel
 
K

KL

Hi,

Try this:

=IF(A1<>"", "Deposited on" & TEXT(TODAY(),"dd-mmm-yyyy"))

or

=IF(A1<>"", "Deposited on" & TEXT(NOW(),"dd-mmm-yyyy"))

Please note that both functions NOW and TODAY are volatile so they will
change as time passes. If you want to fix the date you will have to copy it
and paste values.

Also, your formula will return FALSE if the cell A1 is blank. To avoid that
do this:

=IF(A1<>"", "Deposited on" & TEXT(TODAY(),"dd-mmm-yyyy"),"")

Regards,
KL
 
D

dominicb

Good morning Lewis Koh

Use this formula :

=IF(ISBLANK(A1)=FALSE,NOW(),"")

and change the format of the cell containing it (Ctrl + 1, selec
Number, Custom and type in the "Type:" box) to:

"Deposited On "dd/mm/yy

HTH

Dominic
 
J

Jerry W. Lewis

Additional info:

This works because Excel dates are stored as # days since 1900 (so 38563
corresponds to 30Jul2005) and time is stored as the decimal fraction of
24 hours (so .68864 corresponds to 4:31 PM. Formatting as Date/Time, as
in the TEXT function reveals this correspondance.

Jerry
 
L

Lewis Koh

so there is no way of fixing the date to the date I key in "A1" other
than typing the fix date manually?
 
K

KL

Hi,

Well, not exactly:

1) semi-manual - select the cell in question and hit Alt+;(semicolon)

2) Using VBA code assocviated with the Change event of the worksheet. Let us
know if you need help putting it together and obviously a bit more of detail
regarding the setup of your sheet..

Regards,
KL
 
L

Lewis Koh

The setup is as below:

A B
1 $100 Deposited on "date which A1 is entered".

I have trouble playing with VBA. Not sure where to start...It's under
Tools>Marco>Visuakl Basic Editor right?
 
Top