Excel How to get the number of days an item was opened from the today's date.

[Nicqdevil]

i have use the formula =today() - D8 but this only works when the field has a date. if the particular filed is blank then under my pending days it will be defaulted as 43145. As shown in excel sheet below. Please advice how am I to ensure that the field are blank until a date is entered in the open field. In the excel sheet below the pending fields shows 43145 event there is no date input in the Open field. Kindly advice

 

[Becky]

Welcome to the forum

You can fix that by incorporating an IF function, like this:

=IF(D8>0,TODAY()-D8,"")

Which means if D8 is greater than 0 (ie has a value) then the formula returns the number of days from the date in D8 until today, and if D8 doesn't contain a value then the formula returns nothing.

 

[Nicqdevil]

Welcome to the forum

You can fix that by incorporating an IF function, like this:

=IF(D8>0,TODAY()-D8,"")

Which means if D8 is greater than 0 (ie has a value) then the formula returns the number of days from the date in D8 until today, and if D8 doesn't contain a value then the formula returns nothing.

Thank you for your kind reply. How do ensure the pending date becomes 0 when a date is entered in the "close" field.

 

[Nicqdevil]

Is there a way in which I could automatically transfer item that are closed to a different tab.

 

[Becky]

Thank you for your kind reply. How do ensure the pending date becomes 0 when a date is entered in the "close" field.

You could do that by adding another IF formula:

=IF(E8>0,0,IF(D8>0,TODAY()-D8,""))

This asks whether there is a date in column E, and if there is the answer is 0. If not, then it goes on to calculate the number of days as before.

Is there a way in which I could automatically transfer item that are closed to a different tab.

I'm not sure what you mean by this, could you give me some more details? What items do you want to transfer?

 

[Nicqdevil]

You could do that by adding another IF formula:

=IF(E8>0,0,IF(D8>0,TODAY()-D8,""))

This asks whether there is a date in column E, and if there is the answer is 0. If not, then it goes on to calculate the number of days as before.



I'm not sure what you mean by this, could you give me some more details? What items do you want to transfer?

Thank you for your reply. U have been very kind. What I meant by my question is, as shown in the excel sheet below, I would like to transfer the item that has a date available in the "close" field, to a new worksheet automatically when I fill up the date in the "close" field.

 

[AmjiBhai]

It's very interesting query, indeed!

Actually there are numerous ways of achieving this. I am afraid the people will either get bored or get confused.
Please let me have your sample excel file, I will return it to you with your desired functionality. And we will discuss in detail in this forum so that other people take the maximum benefit.

 

[Becky]

Thank you for your reply. U have been very kind. What I meant by my question is, as shown in the excel sheet below, I would like to transfer the item that has a date available in the "close" field, to a new worksheet automatically when I fill up the date in the "close" field.

Ah ok so you want the row deleted from one sheet and pasted onto another? You will need a macro to do that, and hopefully @AmjiBhai will be able to help you with it

 

[AmjiBhai]

Thank you Becky for motivating me....

Nicqdevil Please copy the following code and paste it in your current sheets (view-code):

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 6 And Target.Row >= 6 Then
Target.EntireRow.Copy Sheets("Archive").Range("A" & Sheets("Archive").Rows.Count).End(xlUp).Offset(1, 0)
End If
End Sub


You can add a confirmation alert too so that before archiving you are reminded that the robotics are watching you.

Next you can delete the entire row by including
target.entirerow.delete

You can also embed a validation check that what you enter as a close-date is a valid date or not.

 

[Becky]

Awesome, good work @AmjiBhai

 

[Nicqdevil]

Thank you Becky for motivating me....

Nicqdevil Please copy the following code and paste it in your current sheets (view-code):

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 6 And Target.Row >= 6 Then
Target.EntireRow.Copy Sheets("Archive").Range("A" & Sheets("Archive").Rows.Count).End(xlUp).Offset(1, 0)
End If
End Sub


You can add a confirmation alert too so that before archiving you are reminded that the robotics are watching you.

Next you can delete the entire row by including
target.entirerow.delete

You can also embed a validation check that what you enter as a close-date is a valid date or not.

Hi AmjiBhai Thank you so much for your kind formula. This is a big help. How do I add alert so that before archiving I would get a reminder and how do I add the validation check on what I enter as a close date is a valid date or not? I am very sorry for many question as I am not very familiar with marcos. Thank you again in advance

 

[Nicqdevil]

in addition to this I would to also like to know how to move back the items from archive to the actual sheet incase an item was sent to archive in error.

 

[AmjiBhai]

OK...First thing first....BTW I personally call this technique or skill set (not just a Macro), it's event-driven programming (if I am not mistaken)...

Adding an Alert is done as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 6 And Target.Row >= 6 Then
if msgbox("Archiving...? " & vbcr & "Are you sure?",vbYesNo)=vbYes then
Target.EntireRow.Copy Sheets("Archive").Range("A" & Sheets("Archive").Rows.Count).End(xlUp).Offset(1, 0)
Target.entirerow.Delete '**
endif
End If
End Sub

ONE WAY IS...
'** its not good to delete, specially when there is chance that you may need it back {therefore use a column which will be used to mark-archived(automatically!), and then you may use conditional formatting to make the item dimmed off}

Then to roll-back the archiving is easily done by double-clicking the dimmed item. It will goto the archive sheet and delete the archived entry and come back to this sheet and un-dim the current item and remove the closed-date.


THE OTHER WAY IS....
Oh...That's the better way....
Let's first check the user has entered a valid date? If yes, that means he wants to archive....

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 6 And Target.Row >= 6 Then
if isdate(target) then

if msgbox("Archiving...? " & vbcr & "Are you sure?",vbYesNo)=vbYes then
Target.EntireRow.Copy Sheets("Archive").Range("A" & Sheets("Archive").Rows.Count).End(xlUp).Offset(1, 0)
'.........Target.entirerow.Delete 'you dont need deleting here
endif

else
gosub rollback_archiving
endif
endif

exit sub

rollback_Archiving:
'here we write routine to delete the row from archive sheet.
Rx=6

do while sheets("Archive").range("A" & Rx)<>""
if sheets("Archive").range("A" & Rx)=activesheet.range("A" & target.row) AND sheets("Archive").range("B" & Rx)=activesheet.range("B" & target.row) then
sheets("Archive").range("A" & Rx).delete
endif
Rx=Rx+1

Loop
Return



End Sub

 

[Nicqdevil]

OK...First thing first....BTW I personally call this technique or skill set (not just a Macro), it's event-driven programming (if I am not mistaken)...

Adding an Alert is done as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 6 And Target.Row >= 6 Then
if msgbox("Archiving...? " & vbcr & "Are you sure?",vbYesNo)=vbYes then
Target.EntireRow.Copy Sheets("Archive").Range("A" & Sheets("Archive").Rows.Count).End(xlUp).Offset(1, 0)
Target.entirerow.Delete '**
endif
End If
End Sub

ONE WAY IS...
'** its not good to delete, specially when there is chance that you may need it back {therefore use a column which will be used to mark-archived(automatically!), and then you may use conditional formatting to make the item dimmed off}

Then to roll-back the archiving is easily done by double-clicking the dimmed item. It will goto the archive sheet and delete the archived entry and come back to this sheet and un-dim the current item and remove the closed-date.


THE OTHER WAY IS....
Oh...That's the better way....
Let's first check the user has entered a valid date? If yes, that means he wants to archive....

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 6 And Target.Row >= 6 Then
if isdate(target) then

if msgbox("Archiving...? " & vbcr & "Are you sure?",vbYesNo)=vbYes then
Target.EntireRow.Copy Sheets("Archive").Range("A" & Sheets("Archive").Rows.Count).End(xlUp).Offset(1, 0)
'.........Target.entirerow.Delete 'you dont need deleting here
endif

else
gosub rollback_archiving
endif
endif

exit sub

rollback_Archiving:
'here we write routine to delete the row from archive sheet.
Rx=6

do while sheets("Archive").range("A" & Rx)<>""
if sheets("Archive").range("A" & Rx)=activesheet.range("A" & target.row) AND sheets("Archive").range("B" & Rx)=activesheet.range("B" & target.row) then
sheets("Archive").range("A" & Rx).delete
endif
Rx=Rx+1

Loop
Return



End Sub

Hi AmjiBhai Thank you again but the instruction that you have provided when I copy it to (view-code)I think I must have used the code wrongly. The code as below triggers a message when a "close" date in input, when I click on no the item remains in the sheet but when I remove the "close" date the item disappears. below are screen shots for your kind referral.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 6 And Target.Row >= 6 Then
If IsDate(Target) Then
If MsgBox("Archiving...? " & vbCr & "Are you sure?", vbYesNo) = vbYes Then
Target.EntireRow.Copy Sheets("Archive").Range("A" & Sheets("Archive").Rows.Count).End(xlUp).Offset(1, 0)
Target.EntireRow.Delete
End If
Else
GoSub rollback_Archiving
End If
End If
Exit Sub
rollback_Archiving:
Target.EntireRow.Delete
Rx = 6
Do While Sheets("Archive").Range("A" & Rx) <> ""
If Sheets("Archive").Range("A" & Rx) = ActiveSheet.Range("A" & Target.Row) And Sheets("Archive").Range("B" & Rx) = ActiveSheet.Range("B" & Target.Row) Then
Sheets("Archive").Range("A" & Rx).Delete
End If
Rx = Rx + 1
Loop
Return
End Sub

item no longer exist in sheet

Thank you and hope to hear from you soon

 

[AmjiBhai]

Sorry for trouble....

please take this code revised and tested as per my own understanding of your requirements..
There are some do's and don'ts

1. Remover the previous code entirely.
2. Don't give it a hard test , as we are in the premature state. Test one thing at a time.
3. Start with entering a valid close date in column F
4. See it is successfully archived. if Yes..remember the number of the item (Column B) say 13
5. Enter x in column F blank row to invoke rollback of archiving and click Yes
6. Enter 13
7. See its successfully arrived back into the issue tracker sheet .
8. See its successfully removed from archived sheet
9. In order to avoid problems its better not to merge cells in any column withing the issue tracker data worksheet.......One record = One XL Row .....must be the rule for safety in data entry.
10. You might also want me to include/maintain the sorting order by item numbers (in column B), please do let me know.



Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 6 And Target.Row >= 6 Then
If IsDate(Target) Then

If MsgBox("Archiving...? " & vbCr & "Are you sure?", vbYesNo) = vbYes Then
Target.EntireRow.Copy Sheets("Archive").Range("A" & Sheets("Archive").Rows.Count).End(xlUp).Offset(1, 0)
Target.EntireRow.Delete
End If

Else
If MsgBox("Do you wish to undo the archiving?", vbYesNo) = vbYes Then
GoSub rollback_Archiving
End If
End If
End If

Exit Sub

rollback_Archiving:
'here we write routine to delete the row from archive sheet.
Rx = 1
fnd = False
bno = InputBox("Book No. (Column B)")
Do While Sheets("Archive").Range("A" & Rx).Row <= Sheets("Archive").UsedRange.Rows.Count + 10
If Sheets("Archive").Range("B" & Rx) = Val(bno) Then
Sheets("Archive").Range("A" & Rx).EntireRow.Copy ActiveSheet.Range("A" & ActiveSheet.Rows.Count).End(xlUp).Offset(1, 0)
Sheets("Archive").Range("A" & Rx).EntireRow.Delete
fnd = True
Exit Do
End If
Rx = Rx + 1

Loop
If Not fnd Then
MsgBox bno & vbCr & "Item not found in archive"
End If
Return

End Sub