+ Reply to Thread
Results 1 to 5 of 5

Date function returns "SAT" when date cell is empty

  1. #1
    Forum Contributor
    Join Date
    06-06-2017
    Location
    Phoenix, AZ
    MS-Off Ver
    2013
    Posts
    129

    Question Date function returns "SAT" when date cell is empty

    Sorry I entered the wrong formula. Post should have read:

    I need to get the Date function that shows day of the week cell (a3) to be empty when the date cell (R3) is blank. I am using the formula =TEXT(A3,”DDD”) in my day of week cell R3. When date cell (A3) is empty, day of week cell (R3) shows "SAT" instead for being blank like the date cell (A3). I don't see any references in the day of week explanation about this problem. I would like help with this.
    Last edited by COURTTROOPER; 06-06-2017 at 04:50 PM. Reason: wrong formula

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,428

    Re: Date function returns "SAT" when date cell is empty

    That's not a valid formula. Please post it all.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,826

    Re: Date function returns "SAT" when date cell is empty

    Isn't this simply because date serial number 0 (blank cells are treated as the number 0) is the serial number for Saturday 0 Jan, 1900? What would you want =TEXT(0,"DDD") to output other than SAT?

    (I know that, technically, 1 Jan 1900 is a Monday, not Sunday. This is due to a long standing "error" that erroneously treats the year 1900 as a leap year).
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  4. #4
    Forum Contributor
    Join Date
    06-06-2017
    Location
    Phoenix, AZ
    MS-Off Ver
    2013
    Posts
    129

    Re: Date function returns "SAT" when date cell is empty

    I figured out how to get the day of the week cell (R3) to go blank. Why it does that is still a mystery to me.
    The fix was to go to cell (A3) which seemed to be empty, but when I went to the cell (A3) and hit the space bar in date cell, the day of the week cell (R3) went blank. I then had to copy cell (A3) to the rest of the date cells and the day of the week cells went blank also. I just don't understand why.

  5. #5
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,826

    Re: Date function returns "SAT" when date cell is empty

    The TEXT() function takes the input value, and applies the number format to the value, then outputs the result as a text string. So =TEXT(A3,"DDD"):
    1) When A3 is any number, the TEXT() treats the number as a date serial number, places the date on its calendar, and outputs the corresponding day of the week.
    2) When A3 is blank, A3 is treated as if it were 0, so, the function outputs SAT, because date serial 0 corresponds to Saturday Jan 0, 1900
    3) When A3 is a space " " (or other text string), the TEXT() function reads the text, applies the number format to the text string (but number formats have no effect on text strings), so the input string is output without modification (you might try using a visible input string like "a" or "dog").

    If you want blank to propagate through the TEXT() function, you will need some kind of IF() function in there to test for the blank. Something like =TEXT(IF(ISBLANK(A3),"",A3),"DDD") or =IF(ISBLANK(A3),"",TEXT(A3,"DDD")) or equivalent.

+ 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. Replies: 2
    Last Post: 08-10-2015, 10:56 PM
  2. [SOLVED] Index function returns 0 (zero) instead of ""(empty)
    By deekshitulu in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-16-2014, 06:52 AM
  3. Format(date,"mm/dd") returns date formatted as "mm-dd"
    By MenacingBanjo in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-16-2013, 09:15 AM
  4. [SOLVED] IFERROR(VLOOKUP) That returns "0" when reference cell is empty
    By Sam Capricci in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 05-04-2013, 01:16 AM
  5. Replies: 1
    Last Post: 09-15-2010, 01:55 AM
  6. Using "IF" with date returns an improper "FALSE"
    By scaffdog845 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-23-2008, 10:31 AM
  7. [SOLVED] For Each Next returns "Empty" when Data in Cell
    By Matt Roberts in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-08-2005, 10:20 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