Getting Rid of Year-1900 Dates from Excel 2016

November 8, 2019 at 07:18:45
Specs: Windows 7
My issue: Column B is formatted with Start Dates (for a project) and columns C, D, and E are formatted to populate projected due dates for various stages of that project. With no dates entered yet (the project is yet to start), columns C, D, and E have become populated with year-1900 dates. Is there a way to remove these dates, without deactivating the formulas in the columns? Thanks

See More: Getting Rid of Year-1900 Dates from Excel 2016

Reply ↓  Report •

#1
November 8, 2019 at 07:27:04
Without seeing your formula, the best I can offer is to try using an IF statement to force the cell to be blank, something like:

=IF ( A1 = "" , "" , Your Formula here)

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#2
November 8, 2019 at 11:43:48
Thanks a lot, Mike - much appreciated!

I am a novice to Excel, so pardon my ignorance. In cell B4, the due date is entered manually, in format YYYY/MM/DD, and the formula in cell C4 is =WORKDAY(B4,C$3). The problem is with cell B5 and onwards, which don't have specific date entered (because project has not commenced), but C5 is populated with 1900 date. Because there are five other columns with due dates, all cells under them similarly have 1900 dates. Many thanks!


Reply ↓  Report •

#3
November 8, 2019 at 13:09:23
Glad I could help and if you have any questions in the future,
Excel questions are best asked in the Office Software forum.

MIKE

http://www.skeptic.com/


Reply ↓  Report •

Related Solutions

#4
November 9, 2019 at 06:34:50
Just a quick note about your WORKDAY() formula.

I noticed that you are not including the Holiday section. You really should,
Not knowing how long your projects can last, nor when they start & end,
it could be possible you encounter a Holiday during the lifetime of a project
and if you do, your formula will return inaccurate information.

It only takes a few moments to make a Holiday list and you can place it anywhere
on your sheet, somewhere out of the way, like cells AA1 thru AA12.

If there are bonuses for finishing early or penalties for being late,
you don't want your formulas to be wrong.

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#5
November 12, 2019 at 05:13:32
Great advice - many thanks!

Reply ↓  Report •

Ask Question