#VALUE! and I can't figure out why!!

[Callaghan86]

Hi guys,

I am using a formula in a spreadsheet at work for a job tracking matrix. It all works fine except for the first line, which returns a #VALUE! error. G6 is the job completion date, which is blank as the job has not yet been completed, and H6 is the deadline date. I am trying to produce the text "Only XX days until deadline" when the deadline day is less than 30 days away. When I run the calculation steps, it says the first 3 out of the 4 statements are true, but is coming up with an error on the fourth (Jobs!H6-TODAY()<30), even when I set the deadline as 30/02/2017 (which is 15 days away).

=
IF(AND(Jobs!G6="",Jobs!H6<>"",Jobs!H6>TODAY(),Jobs!H6-TODAY()<30),"Only"&(Jobs!H6-TODAY())&" days until deadline",
IF(AND(Jobs!G6="",Jobs!H6<>"",Jobs!H6>TODAY()),"Within deadline.",
IF(AND(Jobs!G6<>"",Jobs!H6=""),"Job completed. No deadline set.",
IF(AND(Jobs!G6<>"",Jobs!G6<jobs!h6),"job completed="" "&(jobs!h6-jobs!g6)&="" "="" days="" early",
IF(AND(Jobs!G6="",TODAY()>Jobs!H6,Jobs!H6<>""),"Task is "&(TODAY()-Jobs!H6)&" days overdue!",
IF(AND(Jobs!H6=TODAY(),Jobs!G6<>""),"Deadline today! Task already complete",
IF(AND(Jobs!H6=TODAY(),Jobs!G6=""),"Deadline today! Task not complete",
IF(AND(Jobs!G6<=Jobs!H6,Jobs!H6<>"",Jobs!G6<>""),"Task completed on time",
IF(AND(Jobs!H6<>"",Jobs!G6="",TODAY()>Jobs!H6),(TODAY()-Jobs!H6)&" days overdue",
IF(AND(Jobs!G6>Jobs!H6,Jobs!G6<>"",Jobs!H6<>""),"Task completed "&(Jobs!G6-Jobs!H6)&" days late",
IF(Jobs!H6="","Please set deadline!",
IF(Jobs!F6="","No date planned",
IF(Jobs!G6="","Task not complete"
)))))))))))))

I've tried many different variants with the same result. I've changed all the cells to date format on the data sheet, and it is still returning this error. It can't seem to subtract TODAY() from a date to give a number of days.

Please help! I spent 2 hours at work this afternoon playing with this.

Thanks.</jobs!h6),"job>

 

[DRSteele]

I suspect you have two problems. First, 2/30/2107 does not exist: February has only 28 days in non-leap years and 29 in leap years. Second, the expression H6-TODAY()<30 should have parentheses around the part with 'the future date less today' so it should be (H6-TODAY())<30

 

[Callaghan86]

OH MY GOD! Now I feel like a right idiot! Of all the dates I could have chosen to test it! Haha. Thank you so much!

 

[DRSteele]

The other day I was cleaning a stain off the carpet but it would not lift out. I went to get a better solvent; when I came back the stain had moved! Turns out it was a sunbeam.

Worry not...forge ahead. And laugh!