Joining String with function in IF()?

[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? 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

[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

"Lewis Koh" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[dominicb]

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

[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

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" <[email protected]> wrote
> in message news:[email protected]...
>
>>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
>>
>>
>
>

[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?

[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


"Lewis Koh" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[KL]

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

KL


"KL" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote
> in message news:[email protected]...
>>
>> 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
>>
>
>

[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?

[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

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" <[email protected]> wrote
> in message news:[email protected]...
>
>>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
>>
>>
>
>

[KL]

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

KL


"KL" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote
> in message news:[email protected]...
>>
>> 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
>>
>
>

[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


"Lewis Koh" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[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

"Lewis Koh" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[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

"Lewis Koh" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[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

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" <[email protected]> wrote
> in message news:[email protected]...
>
>>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
>>
>>
>
>

[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


"Lewis Koh" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[KL]

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

KL


"KL" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote
> in message news:[email protected]...
>>
>> 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
>>
>
>

[KL]

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

KL


"KL" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote
> in message news:[email protected]...
>>
>> 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
>>
>
>

[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

"Lewis Koh" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[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


"Lewis Koh" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[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

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" <[email protected]> wrote
> in message news:[email protected]...
>
>>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
>>
>>
>
>

[KL]

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

KL


"KL" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote
> in message news:[email protected]...
>>
>> 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
>>
>
>

[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


"Lewis Koh" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[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

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" <[email protected]> wrote
> in message news:[email protected]...
>
>>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
>>
>>
>
>

[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

"Lewis Koh" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[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


"Lewis Koh" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[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

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" <[email protected]> wrote
> in message news:[email protected]...
>
>>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
>>
>>
>
>

[KL]

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

KL


"KL" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote
> in message news:[email protected]...
>>
>> 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
>>
>
>

[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

"Lewis Koh" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[KL]

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

KL


"KL" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote
> in message news:[email protected]...
>>
>> 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
>>
>
>

[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


"Lewis Koh" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[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

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" <[email protected]> wrote
> in message news:[email protected]...
>
>>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
>>
>>
>
>

[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

"Lewis Koh" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[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

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" <[email protected]> wrote
> in message news:[email protected]...
>
>>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
>>
>>
>
>

[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


"Lewis Koh" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[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

"Lewis Koh" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[KL]

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

KL


"KL" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote
> in message news:[email protected]...
>>
>> 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
>>
>
>

[KL]

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

KL


"KL" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote
> in message news:[email protected]...
>>
>> 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
>>
>
>

[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


"Lewis Koh" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[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

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" <[email protected]> wrote
> in message news:[email protected]...
>
>>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
>>
>>
>
>

[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

"Lewis Koh" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[KL]

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

KL


"KL" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote
> in message news:[email protected]...
>>
>> 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
>>
>
>

[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

"Lewis Koh" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[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

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" <[email protected]> wrote
> in message news:[email protected]...
>
>>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
>>
>>
>
>

[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


"Lewis Koh" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[KL]

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

KL


"KL" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote
> in message news:[email protected]...
>>
>> 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
>>
>
>

[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

"Lewis Koh" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[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


"Lewis Koh" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[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

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" <[email protected]> wrote
> in message news:[email protected]...
>
>>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
>>
>>
>
>

[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

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" <[email protected]> wrote
> in message news:[email protected]...
>
>>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
>>
>>
>
>

[KL]

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

KL


"KL" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote
> in message news:[email protected]...
>>
>> 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
>>
>
>

[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


"Lewis Koh" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[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

"Lewis Koh" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[KL]

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

KL


"KL" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote
> in message news:[email protected]...
>>
>> 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
>>
>
>

[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


"Lewis Koh" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[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

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" <[email protected]> wrote
> in message news:[email protected]...
>
>>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
>>
>>
>
>

[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

"Lewis Koh" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[KL]

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

KL


"KL" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote
> in message news:[email protected]...
>>
>> 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
>>
>
>

[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


"Lewis Koh" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[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

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" <[email protected]> wrote
> in message news:[email protected]...
>
>>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
>>
>>
>
>

[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


"Lewis Koh" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[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

"Lewis Koh" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[KL]

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

KL


"KL" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote
> in message news:[email protected]...
>>
>> 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
>>
>
>

[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

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" <[email protected]> wrote
> in message news:[email protected]...
>
>>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
>>
>>
>
>

[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


"Lewis Koh" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[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

"Lewis Koh" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[KL]

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

KL


"KL" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote
> in message news:[email protected]...
>>
>> 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
>>
>
>

[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

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" <[email protected]> wrote
> in message news:[email protected]...
>
>>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
>>
>>
>
>

[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

"Lewis Koh" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[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


"Lewis Koh" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[KL]

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

KL


"KL" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote
> in message news:[email protected]...
>>
>> 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
>>
>
>