calculate date

I

Icechoco

Qns 1) I have a column eg. Column A. Dates are entered manually into
this column. Such as 31.01.06. I would like a pop up message box to
indicate that work is overdue after 10 days from this date. How do i do
it??

Qns 2) I have a column eg. Column B. Dates are entered manually into
this column. Such as 01.02.06. Another date also manually entered into
another Column C. How do i calculate the difference in the number of
days between Column B & Column C?
 
A

aidan.heritage

If you want a pop up box, you need VBA, but if colour coding would do
conditional formatting will do it - however, it won't work if you enter
what you are calling a date as 31.01.06 as this isn't recognised as a
date by Excel - use a / or - instead of the .

answer 2 is =c-b (have the cell formatted as number)
 
A

Arvi Laanemets

Hi


If you want a pop up box, you need VBA, but if colour coding would do
conditional formatting will do it - however, it won't work if you enter
what you are calling a date as 31.01.06 as this isn't recognised as a
date by Excel - use a / or - instead of the .
Click to expand...

You are wrong here - it all depends on your regional settings.
 
A

Arvi Laanemets

Hi

The difference between dates is calculated as simple substraction
=C1-B1
, and format as number. Or
=DATEDIF(B1,C1,"d")
NB!

To calculate number workdays between 2 dates you can use NETWORKDAYS
function (with Analysis Toolpack installed)

PS. Unlike DATEDIF or date substraction, NETWORKDAYS includes both start and
end days.
=TODAY()-TODAY() returns 0
=DATEDIF(TODAY(),TODAY(),"d") returns 0
=NETWORKDAYS(TODAY(),TODAY()) returns 1 on workday
 
Top