Joining String with function in IF()?
 

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

Can I join a string like "Deposited on" with a function like above? I
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 the
date out.Pls help


--
Lewis Koh
------------------------------------------------------------------------
Lewis Koh's Profile: http://www.excelforum.com/member.php...o&userid=25712
View this thread: http://www.excelforum.com/showthread...hreadid=391511

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

"Lewis Koh" wrote
in message ...

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

Can I join a string like "Deposited on" with a function like above? I
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 the
date out.Pls help


--
Lewis Koh
------------------------------------------------------------------------
Lewis Koh's Profile:
http://www.excelforum.com/member.php...o&userid=25712
View this thread: http://www.excelforum.com/showthread...hreadid=391511

Good morning Lewis Koh

Use this formula :

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

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

"Deposited On "dd/mm/yy

HTH

DominicB


--
dominicb
------------------------------------------------------------------------
dominicb's Profile: http://www.excelforum.com/member.php...o&userid=18932
View this thread: http://www.excelforum.com/showthread...hreadid=391511

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

KL wrote:

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

"Lewis Koh" wrote
in message ...

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

Can I join a string like "Deposited on" with a function like above? I
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 the
date out.Pls help


--
Lewis Koh
------------------------------------------------------------------------
Lewis Koh's Profile:
http://www.excelforum.com/member.php...o&userid=25712
View this thread: http://www.excelforum.com/showthread...hreadid=391511

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


--
Lewis Koh
------------------------------------------------------------------------
Lewis Koh's Profile: http://www.excelforum.com/member.php...o&userid=25712
View this thread: http://www.excelforum.com/showthread...hreadid=391511

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


"Lewis Koh" wrote in
message ...

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


--
Lewis Koh
------------------------------------------------------------------------
Lewis Koh's Profile:
http://www.excelforum.com/member.php...o&userid=25712
View this thread: http://www.excelforum.com/showthread...hreadid=391511

sorry, you need to hit "Ctrl+;" (not Alt+;) in the first solution.

KL


"KL" wrote in message
...
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


"Lewis Koh" wrote
in message ...

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


--
Lewis Koh
------------------------------------------------------------------------
Lewis Koh's Profile:
http://www.excelforum.com/member.php...o&userid=25712
View this thread:
http://www.excelforum.com/showthread...hreadid=391511

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
ToolsMarcoVisuakl Basic Editor right?


--
Lewis Koh
------------------------------------------------------------------------
Lewis Koh's Profile: http://www.excelforum.com/member.php...o&userid=25712
View this thread: http://www.excelforum.com/showthread...hreadid=391511