Date_Update

[ananthram]

Hi All Excel Ninja's,


Please help me with the date updating with the help of formula, where i update this tracker daily.


1). when i update the Date of order column, there the date should be blank when

G column is [code]Done and should not be date value when its Not Done

.


2). when i check the other day it should not change its date format to the existing day that happens when i use =today()[/code] formula.


Please check the sample file below and help me on this


https://www.box.com/s/cb4uw1vvyqrxmnd6zd6o


Thanks in Advance,.

Ananthram

 

[ananthram]

still no one answered my query

 

[sgmpatnaik]

@ananthram


Hi


i am little bit confuse on your question


you have mentioned the dates in the Column A Specific with Order Date and Column B Date Received on and in the Column G you mention as Done / Not Done then where you want to insert the date if the G Column is Done


as per my understanding when G Column is Done then the Column B is auto display the Done Date if is it right then please give the formula in Column B as


=IF(G2="Done",TODAY(),IF(G2="Not Done","",""))


Hope it will solve your problem other wise please inform


Thanks


SP

 

[ananthram]

thanks for your quick reply sir... but now forget about col B completely... what i need is when i update the tracker and if G column shows "done" then the col A's date should be blank and when its "Not Done" then it should enter the existing day's date.


And when i see the same file tommorrow or the other days it should not change the date of existing day this happens when i use =today() in the formula.

 

[Faseeh]

@ Ananthram,


Dear, i don't think this is possible with formula because you will be entering a formula to show or hide date in col B and that will be using TODAY() function of course. Besides that you also need that TODAY() not to be updated the next day the file is opened.


An idea could be to use a helper column and writer manually the date in that column. later you can delete the formula in the adjacent cell. otherwise ask for a macro.


My VBA-Sense took to write something for you, add a helper column between "Date of Order" & "Date Recieved on" and place this in a module Pressing Alt+F11:

[pre]

Sub TestMacro()
Dim i As Integer
For i = 1 To 100
If Cells(i, 8) = "done" Then Cells(i, 2) = ""
If Cells(i, 8) <> "done" Then Cells(i, 2) = Cells(i, 1)
Next i
End Sub

[/pre]

When you run this macro it will copy the dates in "Date of Order" that you will be calculating with TODAY() function but without any formula and hence they will not be updated every next day you open this file.


Hopefully it helps (someone else might write a better code for you as well!!!)


Thanks,

 

[Tareen]

Hi, I have a little bit differnt problem and need your help. I have to add days based on some crietaria, like if user select option of Shipment via Sea, then I wanted to add 6 weeks in current date.

 

[bobhc]

Tareen


It is not the best idea to post your unrelated questions in some one elses. Others will not see it as the original post has been answered or people have already looked and find no interest in the subject so will not revisit.

New question = new post + clear title to get the message problem over to the forum members.

 

[ananthram]

@Faseeh


Sir, thanks for your quick reply. I will try once on this macro and let you know...

 

[ananthram]

@Faseeh


Actually sir i dont need a helper column for this please let me know if i can do this through any formula or some other Macro with a helper column.

 

[Faseeh]

@ Anarthram,


Thanks for your reply! Another idea could be to use a cell reference in place of TODAY() where you put today's date manually and then do the same stuff i mentioned in my earlier post (with or without the code) as you like. What do you say??


PS: There is no need for "Sir", since you appear Indian you can safely use "Bhai" instead


Regards,