Excel 2000 If' Statement w/Date

Microsoft Microsoft excel 2000 95/98/me/...
August 18, 2010 at 22:22:18
Specs: Windows 7
I have user form entering information into database. In the database I want to put the date and time into next column. If there is something in data column put the date and time (in 2 columns ?) if nothing there put "" .

Trying : =IF(P9>=0,"(today)","") but this only puts text in cell 'today'.

What have I got wrong. Have tried a few other combinations without luck.

See More: Excel 2000 If Statement w/Date

Report •

August 19, 2010 at 06:00:52

You have "(today)" inside double quotes.
Excel treates text inside these double quotes as text - hence you get (today) displayed in the cell.

The Excel function is TODAY() (no quotes)
The formula becomes: =IF(P9>=0,TODAY(),"")
This will likely show as a number such as 40409
40409 is the Excel date number for 19 August 2010

You can then format the cell with a date format such as dd/mmm/yy

You can use NOW() to get date and time:
then format something like this: dd/mmm/yy hh:mm

There is also a problem with how you have used IF to test for an empty cell.
Empty cells often get treated as containing zero - Excel does a lot of 'implicit' conversions.
You IF function test is TRUE for empty cells - which is not what you want.

Try this: =IF(P9<>"",NOW(),"")

An alternative is to test if there is a number in the cell: =IF(ISNUMBER(P9),NOW(),"")

There is one other issue regarding using TODAY() or NOW() - both are 'volatile' functions, which means that they re-calculate every time any cell on the worksheet changes. This limits their use for time- or date-stamping.

It is possible to use a Visual Basic macro to time- or date-stamp cells. If that is what you need please look at this post
and come back if you need more help.


Report •

August 19, 2010 at 15:22:45
Hi Humar

Thanks for the reply. Very good. I did not realise that the dates would change (volotile) when entries made to sheet,

You mentioned date stamp. Is there a function for that ?

I will do some searching on Google.

Report •

August 20, 2010 at 04:15:12

Look at response #4 in the post I provided a link to.

It shows how to use Visual Basic and Excel's change events to time-stamp or date-stamp data when it is entered or changed.


Report •

Related Solutions

Ask Question