Using EDATE and default 1900 date system

[Defyrider2]

I am starting to use Excel more in a new position, but still consider myself a novice.

I am creating a tracking matrix for training. I have a couple of problems:

I am using EDATE to provide future dates for required training. When I enter the formula =EDATE(D15,6), I get default dates of 06/1900 in all the blank cells. This is causing the status cells using =IF(TODAY()<D15,"","PAST DUE") to show they are past due when in fact they are not in use.

I attached the file for review. Any help would be greatly appreciated.

Thanks.

 

[Somendra Misra]

Hi,

Welcome to chandoo.org forum.

Excel treats dates as serial numbers starting with 1. And 1 is 1-Jan-1900. So if column C is blank it is calculating 6 months from that.

See the attached file I had added an IF check for all the columns.

Regards,

 

[eibi]

Defyrider,

Welcome to the forum.

Somendra,

Just out of curiosity, is there a technical difference between:

[@[Training Date]]=""​

and

ISBLANK([@[Training Date]])​

 

[Somendra Misra]

Defyrider,

Welcome to the forum.

Somendra,

Just out of curiosity, is there a technical difference between:

[@[Training Date]]=""​

and

ISBLANK([@[Training Date]])​

I think if the data entry is manual than there is no difference because the cell by default is blank, but if there is a formula returning "" (null string) than the result will be different.

Regards,

 

[Defyrider2]

Hi,

Welcome to chandoo.org forum.

Excel treats dates as serial numbers starting with 1. And 1 is 1-Jan-1900. So if column C is blank it is calculating 6 months from that.

See the attached file I had added an IF check for all the columns.

Regards,

That did the trick. Thank you so much for your help!

 

[Defyrider2]

One more item. I am using the VLOOKUP function to fill in the department based on the individuals name. It would also be very helpful if the ACTIVE status could also be listed. All 3 items come from the EMPLOYEE DATA TABLE. I have tried using the VLOOKUP and also an INDEX AND MATCH array but can't get it to work. I attached the file for review. Thanks in advance for any help provided.

 

[Somendra Misra]

See this file.

Regards,

 

[Defyrider2]

See this file.

Regards,

Thank you again