Having a problem with weekday function not returning the right day

[Caylif]

Hello I am trying to do 3 things in this formula
=IF(OR(IFNA(MATCH(C3,$C$4:$C$505,0),""),YEAR(C3)<>YEAR(StartDay)),WEEKDAY(C3),"")
1.look at column c3:c505 and add blanks in column d when there is a duplicate MATCH(C3,$C$4:$C$505,0),""
2. have 1 year StartDay=01/6/2018 so if year in column C does not = year of StartDay then also "" YEAR(C3)<>YEAR(StartDay),Weekday(c3),"")
3. last if both are true then input weekday(c?) in column D Monday, Tuesday, Wednesday...

Column C is list of Dates Column D is Formula

This is the Question. I am getting a output of 1/1/1900 instead of Weekday(C). Anyone know Why? I am relatively new at Excel and this is probley a really simple fix lol

Column C Column D
c3:c505
4/1/2018 1/1/1900
7/1/2018 1/1/1900
9/2/2018 1/1/1900
12/2/2018 1/1/1900
6/3/2018 1/1/1900
2/4/2018 1/1/1900
3/4/2018 1/1/1900
11/4/2018 1/1/1900
8/5/2018 1/1/1900
5/6/2018 1/1/1900
1/7/2018 1/1/1900
10/7/2018 1/1/1900
4/8/2018 1/1/1900
7/8/2018 1/1/1900


Thanks

[Jonmo1]

Format the cell with the formula as a number, not a date.

[Caylif]

getting a value of 1 in column D

[Jonmo1]

And the weekday function doesn't return the TEXT string version of the weekday, like Monday Tuesday etc.
It only returns the number. Sunday=1 Monday=2 etc..

If you want the spelled out weekday word, try replacing WEEKDAY(C3) with TEXT(C3,"dddd")

[Caylif]

oh ok It was a easy fix Thanks

[Jonmo1]

You're welcome.

[Caylif]

ok now I am having another problem all but the Text(c3,"dddd") isn't working. I removed the ifna and match and =IF(YEAR(C3)<>YEAR(StartDay),"",WEEKDAY(C3)) and it works its the other part, that isn't now. If I use the remove duplicates in data tab, when a reference changes will it automatically remove duplicates still?

[Jonmo1]

Sorry, to this point I've only looked at the output of the weekday, I gave no thought to the overall function of the formula.

Can you explain in words what the formula is supposed to actually do ?

do I understand right,

If the date already exists, return ""
If the year doesn't match the year of StartDate, return ""
Otherwise return the weekday ?

[Caylif]

yes that is right

[Jonmo1]

Try

=IF(OR(YEAR(C3)<>YEAR(StartDay),COUNTIF(C$3:C3,C3)>1),"",TEXT(C3,"dddd"))

[Caylif]

its doing the same thing as =IF(YEAR(A5)<>YEAR(StartDate),"",TEXT(A5,"dddd"))Count if isn't doing anything

[Jonmo1]

I think you need to attach a sample book, and show your expected results.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Caylif]

Figured it out first row =if(year(a1)<>Year(weeklyPD),"",Text(a1,"dddd") this gets rid of just the preceding year dates second row =IF(AND(YEAR(A2)<>YEAR(WeeklyPD),A2=A1),"",TEXT(A2,"dddd"))this is the
One that I drag down. This gets rid of the years I don't want and checks for duplicates both also adds date name
Thanks for your help

[JohnTopley]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.