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

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>
 
Last edited:

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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
 
Last edited:
Upvote 0
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!
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top