• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculate overdue days

S

sveinhelge.urdal

Member
I have two cells with dates and want excel to calculate how many days overdue i am in my woorkload.

Eksample:


A1 contains the start date

B1 next due date

C1 I want to show how many days I'm overdue.


I have managed to shov the number of days by useing this formula:

=DATEDIF(B1;TODAY();"d")


Problem is that whwen the task is completed I want to be abel to leave the due date cell blank (B1) but then the formula in C1 returns the answer 40960.

Allso when I'm not overdue yet the formula returns an #NUM! error.


How can I make the cell blank if not overdue and returneing the value "OK" when cell B1 is blank?
 
Hi,


Try out this formula:


=IF(ISBLANK(B2)=TRUE,"",IF(TODAY()>B2,"",B2-TODAY()))


It gives blank if you delete the due date, it gives blank if next due is exceeded.


Regards,

Faseeh
 
Thanks!

Only one small problem. It returns overdue dates with a negative sign in frint. Ex: -11

Thats OK.

But it also returns number of days left to date and I don't want that to show.
 
Hi,


Check out this one:


=IF(ISBLANK(B2)=TRUE,"",IF(TODAY()>B2,"",-B2+TODAY())) for negative sign.


Well if you don't want nos of days remaining then what is the purpose of formula? Can u please clarify?
 
I don't wanty number og remaining days because the list is supposed to show overdue. The list is to be used to send to people to remind them of completing the task.
 
I think i am unable to explain myself? ;)


Lets assume start date is 18 Feb 12 in cell A1, Next due date is 25 Feb in B1 and today is 21st Feb. What do u wan to be displayed for day in cell C1?
 
Hi, sveinhelge.urdal!


Sorry for arriving at dessert-time. I think that the formula at first Faseeh post might be:

=IF(ISBLANK(B2)=TRUE,"",IF(TODAY()>B2,B2-TODAY(),""))

Should it work?


Regards!


PS: related to DATEDIF function, I'd suggest to check out this topic where are explained unresolved bugs about it.

http://chandoo.org/forums/topic/excel-2007
 
Hi SirJB7,


Nice to see you back, where have been for last few day?? I think you were busy in some really tough assignment? :D


Regards,

Faseeh
 
@Faseeh!

Thanks for your greetings... and you're right. I've been hardly occupied for a few days... trying to keep beach's sand out of my feet :D

Regards!
 
You must log in or register to reply here.
Back
Top