+ Reply to Thread
Results 1 to 9 of 9

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

  1. #1
    Registered User
    Join Date
    06-21-2016
    Location
    Vilnius
    MS-Off Ver
    Windows 10
    Posts
    3

    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?

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    17,335

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

    WEEKDAY does not like "." full stops : you need to change to "/" or"-"
    Last edited by JohnTopley; 06-21-2016 at 08:33 AM.

  3. #3
    Valued Forum Contributor
    Join Date
    05-08-2015
    Location
    Uvalde, TX
    MS-Off Ver
    2010
    Posts
    720

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

    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

  4. #4
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

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

    Try

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

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    17,335

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

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

  6. #6
    Registered User
    Join Date
    06-21-2016
    Location
    Vilnius
    MS-Off Ver
    Windows 10
    Posts
    3

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

    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

  7. #7
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

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

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

    Sunday = 1
    Monday = 2
    etc
    Saturday = 7

  8. #8
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

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

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

    Try
    =TEXT(B2,"dddd")

  9. #9
    Registered User
    Join Date
    06-21-2016
    Location
    Vilnius
    MS-Off Ver
    Windows 10
    Posts
    3

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

    Thank you all for your help, learned a lot

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. "Please enter a date in the format dd/mm/yyyy" error
    By marcelotero in forum Excel General
    Replies: 2
    Last Post: 08-18-2015, 12:23 PM
  2. [SOLVED] VBA Get the Date Format Change As "MM-DD-YYYY" In Range
    By breadwinner in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-20-2014, 10:37 AM
  3. Replies: 1
    Last Post: 12-11-2013, 10:35 AM
  4. [SOLVED] Convert from "general" (YYYYMMDD) to specific "custom" format (YYYY.MM.DD)
    By Ella_p in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-10-2013, 02:23 AM
  5. Replies: 3
    Last Post: 08-13-2013, 06:25 AM
  6. Replies: 0
    Last Post: 08-09-2012, 04:31 PM
  7. convert string to date format of "d-mmm-yyyy"
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 12-12-2011, 09:48 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1