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
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