Using Today() and making it Static based on a particular condition. Part-II

all4excel · 05-30-2008, 05:49 AM

I have started a New thread as the previous thread was closed.

http://www.excelforum.com/showthread.php?t=645281

Using Today() and making it Static based on a particular condition.

Roy,

Its not working in the cell G2 and H2

[ =IF(ISBLANK($F2),"",IF($F2="Completed",staticdate(),TODAY())) ]

[ =IF(ISBLANK($F2),"",IF($F2="Completed",statictime(),NOW())) ]

As explained earlier there are three things in the dropdown in Column F in the Sheet "Sarah".

The Column F has a Drop-down with three options
1. Default Blank Option
2. In Progress ---It will lead to TODAY() in Column G and NOW() in Column H
3. Completed

Kindly explain in details as to how to use this function in other Workbooks in Details.

Please reply ...!

**royUK** · 05-30-2008, 06:02 AM

The Functions are not used in those cells.

Column H is headed actual time, from this I would expect the time to change.

Column G has no formula & appeared to be a simple date input.

all4excel · 05-30-2008, 06:20 AM

The date and time in the columns G and H are based on the values in the dropdown menu.

Is it the UDf is designed to be used in the column B based on a Blank value in Column A.?

If Im selecting "Completed" in the Column F then the time should not change the next date, the way you have made static date and same fpr the date so that then I can take the exact total time taken for the task..

**royUK** · 05-30-2008, 06:24 AM

You just need to build a formula that checks F2 & uses the UDFs

In G2

=IF(F2="Closed",staticdate(),"")

In H2

=IF(F2="Closed",statictime(),"")

all4excel · 05-30-2008, 06:42 AM

Originally Posted by royUK

You just need to build a formula that checks F2 & uses the UDFs

In G2

=IF(F2="Closed",staticdate(),"")

In H2

=IF(F2="Closed",statictime(),"")

I have tried this but its not working, the result doesn't remain static.

Its not working in the cell G2 and H2

[ =IF(ISBLANK($F2),"",IF($F2="Completed",staticdate( ),TODAY())) ]

[ =IF(ISBLANK($F2),"",IF($F2="Completed",statictime( ),NOW())) ]

**royUK** · 05-30-2008, 07:24 AM

F" does not have Completed as an option. Your formulas work when you change it to Closed

**Simon Lloyd** · 05-30-2008, 07:51 AM

Check out the code in the worksheet module for sarah it will do what you want without formula!

**Simon Lloyd** · 05-30-2008, 08:11 AM

Slight mistake in attachment, this one works perfect!

all4excel · 05-30-2008, 10:32 AM

Originally Posted by Simon Lloyd

Slight mistake in attachment, this one works perfect!

Dear Simon,

This works well only for "Completed" option, however not for "In Progress" or Blank options.

The three options in the Drop-down...
1. Blank - = if(isblank(f2),"",

2. In Progress = I want the normal TODAY() or Now as the time should keep on updating for this selection.

3. Completed/Closed - The time should be Static

I would appreciate if you could make the Statictime () and Staticdate() functions work for those columns.

I reiterate that these UDF's were fine except that it was not working for the dropdowns differently..