Auto Insert Date and time and calculate Age based .

**SANU PN** · 07-14-2014, 01:02 AM

Dear all..

Kindly help me with macro for the below requirement..

I have an Excel sheet..
Where the column "J" is a status column
K - date Column
L- Time Column
M- TAT Column
N- Age Column

When I enter the word as "Pending" in Column "J", I should get the Age in Column "N" with reference to current date and it should change on next day and so on.

When I enter the word as "Closed" in Column "J", I should Get that Static Date in "K" and Static Time in "L" and Column "N" should go blank.

Regards
SANU PN

**venkat1926** · 07-14-2014, 02:02 AM

what I am giving is an event code (not standard macro)

right click the sheet tab
click view code
in the window that comes up copy paste this event code and save the file as macro enabled file

Please Login or Register  to view this content.

now go to the sheet in column j (any blank cell) type Pending (be careful about spelling)
see what happens to column N in that row , that will be today's day and will change tomorrow
in any other cell in col J if you type "closed" what happens to col K to N in that row

test and confirm to newsgroup

**HaHoBe** · 07-14-2014, 02:13 AM

Hi, venkat1926,

see what happens to column N in that row , that will be today's day and will change tomorrow

As you put in a static date that should never change, AFAIK you would neeed to insert a Formula there.

And I´m afraid the area in Column L and M should not be ckeared but converted to static Date and Time and only Column N be cleared.

Ciao,
Holger.

**SANU PN** · 07-15-2014, 02:03 AM

Hi..

Looks not working for my requirement..
I will explain the requirement here..

I have an Excel sheet..

Where the column "J" is a status column
K - date Column
L- Time Column

When I enter the word as "Pending" in Column "J", I should get the difference of Column "C" with Current Date in "O" Also Difference of Column "D" with Current Time in "P"

When I enter the word as "Closed" in Column "J", I should Get that Static Date in "K" and Static Time in "L" and Column "N" & "O" should go blank
Also should calculate Column "C" -"K" in Column "M" and calculate Column "D" -"L" in Column "N"

Regards
SANU PN

**HaHoBe** · 07-15-2014, 02:08 AM

Hi, SANU PN,

please attach a sample workbook for us to test (I´m not so sure about the Forumlas you have inserted inside the cells) and please make sure on one sheet situation as is and on another one as requested. Please fill in some of the formulas to give us a clou what to insert as formula.

Ciao,
Holger

Auto Insert Date and time and calculate Age based .

LinkBack

Thread Tools

Rate This Thread

Display

Auto Insert Date and time and calculate Age based .

Re: Auto Insert Date and time and calculate Age based .

Re: Auto Insert Date and time and calculate Age based .

Re: Auto Insert Date and time and calculate Age based .

Re: Auto Insert Date and time and calculate Age based .

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Calculate time remaining based on end date & time and current date

Complex VBA problem auto insert date and time, auto insert comment, etc.

Insert Auto Date & Time Stamp

Auto insert date+time with value in cell

Auto Insert System Date & Time

Bookmarks

Bookmarks

Posting Permissions