Solved How do I get rid of year-1900 dates from excel? Thanks.

November 12, 2019 at 05:21:40
Specs: Windows 7
In my spreadsheet, 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. 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 dates entered (because project has not commenced), but columns C, D, and E have become populated with year-1900 dates.

Thanks!

message edited by Kafka


See More: How do I get rid of year-1900 dates from excel? Thanks.

Reply ↓  Report •

#1
November 12, 2019 at 06:38:30
Didn't we just answer this question in this thread?
https://www.computing.net/answers/w...

B4 is the Start Date, so
what is in cell C$3?
Your formula is =WORKDAY(B4,C$3)


MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Reply ↓  Report •

#2
November 12, 2019 at 08:10:44
✔ Best Answer
I've played around a bit with this and the answer is still an IF statement:

=IF(B4="","",WORKDAY(B4,C$3,$AA$1:$AA$12))
=IF(B5="","",WORKDAY(B5,C$3,$AA$1:$AA$12))
=IF(B6="","",WORKDAY(B6,C$3,$AA$1:$AA$12))

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#3
November 12, 2019 at 10:10:56
Ok, thanks a bunch - I will apply this and see how it works out.

Yes you did answer it earlier, Mike, but I thought this was a different forum, specific to MS Office issues. Apologies for dual posting - the solution provided earlier had not worked for me - most likely due to some oversight on my part.

Regards and best wishes.


Reply ↓  Report •

Related Solutions

#4
November 12, 2019 at 10:15:36
Cell C$3 has a number in it - e.g. "5", which represents the number of days allowed for the task started on the B4 start date.

Reply ↓  Report •

#5
November 12, 2019 at 10:31:21
Kafka,

the solution provided earlier had not worked for me

If an answer does not work for you please do not hesitate to reply and ask why
Since I can not see your worksheet, it may be that I mis-interpreted your question,
or not enough information was posted for an accurate answer.
There are several alternative ways of doing everything in Excel, so explaining your problem, using Column Letters, Row Numbers and what each column represents always helps.

If you wish to post an example of your data see this How-To, which explains the use of < PRE > tags:
https://www.computing.net/howtos/sh...

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Reply ↓  Report •

Ask Question