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

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

I have a Sheet in which I keep a record of all the Task Assigned to my Team Mates.I dont write the date in this Sheet I get it by the virtue of TODAY(), however today is a Dynamic function so it keeps on updating everyday..

I would make it blank by "" when there are no Tasks mentioned in the Column D, however if there's something mentioned in the Column D then the Current date should become static as I would need to keep a track as when this Task was Assigned to a team mate..

One more thing is to avoid getting weekends like Saturday/Sundays
or any Public Holidays. Is that possible?

Like for instance the Function Today() would be used to get the dates excluding any Weekends or Holidays and it should become static when Any Data is entered in Column D. So, if there's no data in column D, then it should remain dynamic displaying the current date.

I want to use the same logic to track the Time for each Completed task shown in the Tab Sarah.

I would appreciate if I could get help in Excel preferably though if its not possible then VBA would also do...

Please find the attachment.

Please anyone
This can help me robust Tracking systems...

Originally Posted by EdMac

Suggest you search the the forums for date/time stamping.

Lots of previous posts on the subject, solutions use VBA

Dear Ed,

I had already tried that earlier as well, however with different "keywords" ofcourse..

However, when u mentioned that I came up with the following Links..

http://www.excelforum.com/showthread...=time+stamping

-----------------------------------------------------------------

http://www.excelforum.com/showthread...=time+stamping

-----------------------------------------------------------------

-----------------------------------------------------------------

I would appreciate if anyone could help me with a User Defined Function as I am not familiar with VBA at all.

Originally Posted by EdMac

UDFs use VBA so no difference.....

Suggest you try out one of the solutions you have found in a copy of your sheet, altering the values to suit, and see how you get on. VBA is the only way to achieve the solution you require, so pluck up your courage and give it a whirl!

I will definitely give it a try..
However, before I could try, just wanted to know whether I can get User Defined Function in this manner where I can pass arguments where the Date should remain Static or Updating as it does for the TODAY().

I will give an Analogy

The Function [ =cell("filename") ] gives you the complete path of the Sheet, however it changes if you open any other file.

In order to avoid this to happen you put a cell address next to the filename.
[ =cell("filename",A1) ] and the path reamins unchanged even while swapping between different sheets.

Like a Modified [ = Today() ] where I pass the values 0 for the regular updation and 1 for making it Static wherever neccessary..

[ = Today(0 or 1) ] and this 0 or 1 can be derived by the simple usage of IF condition...

In my case, if this were possible [ = Today(if(not(isblank(A2)),1,0) ]

NOOB,

I am putting the code and wrapping it this time so kindly do not delete this threads..

http://www.excelforum.com/showthread...=time+stamping

Code-
-------------------------------------------------------------

HTML Code: 

-------------------------------------------------------------

http://www.excelforum.com/showthread...=time+stamping
-----------------------------------------------------------------
Simon Lyod's Code

HTML Code: 

-----------------------------------------------------------------

http://www.excelforum.com/showthread...=time+stamping

-----------------------------------------------------------------

HTML Code: 

-----------------------------------------------------------------

I used this code from VBA Noobs solution in the following thread.

http://www.excelforum.com/showthread...=time+stamping

HTML Code: 

Originally Posted by royUK

I have added a couple of User Defined Functions & amended the existing formulas to use these.

Can u please elaborate on How I use these functions as I do not know to use VB..

Thanks kindly explain if possible.

I am not able to use the functions so kindly provide the explanation as its not working maybe I dont know how to make it work...

I dont know what USER defined function u mentioned in the thread..

Bump.........

Originally Posted by royUK

The code is in the workbook, open the VB Editor to see, & the formulas are in the sheet. See the formula in C2- =IF(ISBLANK($E2),"",statictime()). The time entered by the formula will not change.

Thanks a million...
U gave me exactly what I was looking for a Function In Excel..

However, how do I make use of this function in other Workbooks..?

Roy,

Its not working in the cell G2 and H2

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

[ =IF(ISBLANK($F2),"",IF($F2="Closed",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. CLosed

More importantly how do i make use of this wonderful User Defined Functio in other worksheets?

Please reply so that this thread can be closed...!