Another Excel Question.....

Sparky83

Prominent Member
Joined
Nov 23, 2008
Messages
1,710
Reaction score
143
Points
395
Location
Crawley
Hi all,

I wonder if anyone can help me, I'm struggling finding the answer on google for a spreadsheet i need to complete for work.

Basically I'm trying (and failing) to create a tracker for jobs that are time critical. What i have is a list of jobs in the first column. Then the date they are due on the next column, then if they are compliant or not in the last column. What i would like to do in the "Compliance column" is show how many days until they become non compliant. i.e. "32 Days left"

Job List Due Date Compliant?
Job 1 15/05/2016 1 day
Job 2 22/05/2016 8 days
Job 3 29/05/2016 15 days
Job 4 05/06/2016 22 days
Job 5 12/06/2016 29 days
Job 6 19/06/2016 36 days
Job 7 26/06/2016 44 days
Job 8 03/07/2016 51 days
Job 9 10/07/2016 Compliant (Shows as Compliant as its over 56 days (8 weeks) from due date)

Any help here would be great as I'm pulling my hair out trying all sorts of IF/IFS formulas and conditional formatting etc etc but just cant work it out.
 
You are clearly a genius!

Thank you.... i don't understand it, but it works a treat!
 
You can remove the INT casting since subtracting one day from another gives a number of days, not a date (or anything else):

=IF(B7-TODAY()>56,"Compliant",(B7-TODAY())&" days")

Just to make it a little less 'mysterious' :)
 
You can remove the INT casting since subtracting one day from another gives a number of days, not a date (or anything else):

=IF(B7-TODAY()>56,"Compliant",(B7-TODAY())&" days")

Just to make it a little less 'mysterious' :)
Agreed. Was going to suggest that, along with another couple of options:

=IF((B7-TODAY())>56,"Compliant",IF(B7<TODAY(),"Non-Compliant",B7-TODAY()))

Format the cell as "#0 days".

The OP hasn't stated what happens if the job is overdue. This suggestion covers that, if required. By including " days" in the cell format, the result can be treated as a number in other formulas, also if required.
 

The latest video from AVForums

Is Home Theater DEAD in 2024?
Subscribe to our YouTube channel
Back
Top Bottom