Indepth Date Counter

Newbienew

Board Regular
Joined
Mar 17, 2017
Messages
245
Office Version
  1. 2016
Platform
  1. Windows
I am looking to make a pretty in-depth date counter using columns "B" and "M" or because it's a chart "Date of Doc Request" and "Date Return". The formula that I attempted to use is in Column "N" or "COUNTER OF DAYS BEFORE RETURNED".
How it works and my aim.

The "Date of Doc Request" has a formula in it that will populate a date when an entry is made in the first column.
From the Date of Doc Request column, the counter will start in the counter column base of a NOW function.

Seems easy enough until I thinking about the issues I'm trying to account for.
If both cells are blank, then nothing needs to happen.
If a date is in the first cell but no date in the other than I need a default that counts from the NOW function.
If there is a date in the second cell then that date takes precedence for the counter
If the date is the same date in both cells resulting in "0" then I need the Counter date to say "Returned Same Day"
If the date entered in the second cells is less than the first cell, creating a - number, I need the counter date to say "Error with dates"
On the same note there is a type of error that I can duplicate but what happens is the date is really high. Like if you put the cell in General format and the date is 44553 for example. This has an effect on the condition format (that I also need help with) that if over 30 days turns the cell red. <Im thinking the CF should be based off if the second cell has a date that the CF will stop regardless if it is over 30 days.

I have other variations of my trying to make this work off to the side highlighted in yellow.

How can I make this work and where did I go wrong?

Book 2
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

fhqwgads

Board Regular
Joined
Jul 17, 2018
Messages
194
Office Version
  1. 2019
Platform
  1. Windows
is the circular reference for your column B formulas not giving you any issues?

excelhelp3.jpg


also for your count of days formula

VBA Code:
=IF(COUNTA([@[DATE RETURNED]]),IF([@[DATE RETURNED]]-[@[DATE OF DOC REQUEST]]="0","SAME DAY RETURN",IF([@[DATE RETURNED]]-[@[DATE OF DOC REQUEST]]<"0","DOC DATE HIGHER THAN RETURN",[@[DATE RETURNED]]-[@[DATE OF DOC REQUEST]])),NOW()-[@[DATE OF DOC REQUEST]])

i dont think you need "" around 0?
 

Newbienew

Board Regular
Joined
Mar 17, 2017
Messages
245
Office Version
  1. 2016
Platform
  1. Windows
No It doesnt give me any issues. Why do you ask?
I used your formula but not seeing how it works.

Book2.xlsx
ABCDEFGHIJKLMN
3IC1 EXAMPLE14-Aug-20NE-RQCT-18/15/2020DOC DATE HIGHER THAN RETURN
4IC1 EXAMPLE14-Aug-20NE-RQCT-28/14/2020DOC DATE HIGHER THAN RETURN
5k16-Aug-20NE-RQCT-38/16/2020DOC DATE HIGHER THAN RETURN
CHIT TRACKER
Cell Formulas
RangeFormula
B3:B5B3=IF(A3<>"",IF(B3<>"",B3,NOW()),"")
N3:N5N3=IF(COUNTA([@[DATE RETURNED]]),IF([@[DATE RETURNED]]-[@[DATE OF DOC REQUEST]]="0","SAME DAY RETURN",IF([@[DATE RETURNED]]-[@[DATE OF DOC REQUEST]]<"0","DOC DATE HIGHER THAN RETURN",[@[DATE RETURNED]]-[@[DATE OF DOC REQUEST]])),NOW()-[@[DATE OF DOC REQUEST]])
 

Newbienew

Board Regular
Joined
Mar 17, 2017
Messages
245
Office Version
  1. 2016
Platform
  1. Windows
Just checking back in on this to see if anyone could still help or explain how to pull this off. Thank you in advance
 

Watch MrExcel Video

Forum statistics

Threads
1,118,269
Messages
5,571,227
Members
412,372
Latest member
JON_ROCKS
Top