Having date data in "yyyy:mm:dd hh:mm:ss" format, =weekday formula does not work

Hi,

I',m having this issue, my data is in this format:

Untitled.jpg

When i use formula =weekday to determine which day of the week it is i get #value

Any suggestions?

Hi Imli,

Is your data in a proper date format?
The picture shows all the dates to the left side of the column. If you didn't align it to the left then it is most likely not a proper date format.

I don't know how you version of excel in Lithuania would normally display a date format.

If you change the format of the cell to general, do the numbers change?

Does: 2016.05.03 20:00:00 change to: 42493.83333? If it doesn't change then it is most likely not in a proper date format.

Once it is in a date format the =weekday will work.

Hope this is helpful.

Cheers

Try

=WEEKDAY(SUBSTITUTE(C1,".","/"))

Unless you need the full stops use FIND/REPLACE to convert your data.

Thank you for help. Replaced "." with "/" but when i use =weekday formula for example on 2016-05-01 i get 1 as a result but in calendar it was sunday:

Untitled.jpg

1 is the correct response for the Weekday Function given a date that is a Sunday.

Sunday = 1
Monday = 2
etc
Saturday = 7

Or are you wanting to actually return the TEXT version of the weekday, "Sunday" ?

Try
=TEXT(B2,"dddd")

Thank you all for your help, learned a lot