Callaghan86
New Member
- Joined
- Feb 14, 2017
- Messages
- 15
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>
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>
Last edited: