Jump to content


Photo

Date Formats

Date

  • Please log in to reply
3 replies to this topic

#1 Zella

Zella

    Member

  • Members
  • PipPip
  • 12 posts

Posted 18 May 2017 - 05:32 PM

I'm working on a weekly timesheet that begins on Sunday and runs through Saturday.  The sheet has two date cells (one for the 1st date of the week and one for the last date of the week).

 

I've attempted to format these date cells as YYYYMMDD using the Custom option of the Format Cells dialog box but all I get are errors (i.e., #########).  I know the cells are large enough to display the dates in this format; I've typed 20170514 and 20170520 in the cells respectively.  What am I doing wrong?  I need this date format.

 

I'm also working on a formula in the 2nd date cell to add six (6) days to the 1st date cell and reflect for the full weeks' date span.

 

i.e.,           Dates Worked

 

From YYYYMMDD To YYYYMMDD

 

Any suggestions, please?



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 831 posts

Posted 18 May 2017 - 06:11 PM

Hi Zella,

 

I suspect that you're seeing problems because Excel's custom format codes are case sensitive. yyyy will work, for example, but YYYY won't.

 

The correct format code for what you need is: yyyymmdd

 

You can see a complete explanation of custom format codes and how to apply them in the video lesson: Essential Skills Lesson 4-4 Create custom number formats.

 

Once you have formatted your dates correctly you can add days with a simple addition formula like this:

=A4+6

 

You can see more about how to add days to a date (and why this works) in the video tutorial: Expert Skills Lesson 3-7 Understand Excel date serial numbers and custom date formats.

 

I have attached an example workbook showing this in practice:

 

Attached File  DatesWorked.xlsx   9.89KB   36 downloads

DatesWorked.png

 

I hope this is the answer you were looking for, but please feel free to reply if you need any more help with this.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#3 Zella

Zella

    Member

  • Members
  • PipPip
  • 12 posts

Posted 25 May 2017 - 08:40 AM

Thank you for your reply.  I've tried what you suggested and it still doesn't work.  I went to Format Cells > Custom > selected a format close to what I need, then modified it in the 'Type' block > OK.  I get the same result:  "##############".  See attached screen shots.  What am I doing wrong?

 

I would prefer a drop down calendar selection tool in Cell R9, but it's too complicated for me to do in Excel 2013 --- I've tried. But I'd settle for the date format I need!

Attached Files



#4 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 831 posts

Posted 25 May 2017 - 10:17 AM

Hi Zella,

 

The reason this isn't working is that Excel doesn't understand 20170514 as a date - it sees this as a number instead.

 

Using a custom format code allows you to change the way Excel displays the date, but you will still need to enter the date in a format that Excel recognizes as a date. If you try entering 2017-05-14 or 14 May 2017, for example, Excel will recognize these as dates and then apply the custom formatting to display them as 20170514.

 

If you really need to enter the date in this format, you can work around this limitation by using the LEFT, RIGHT, MID and DATE functions.

 

You can see how to use LEFT, RIGHT and MID in the video lesson: Expert Skills Lesson 3-20 Extract text from fixed width strings using the LEFT, RIGHT and MID functions.

The DATE function is explained in the video tutorial: Expert Skills Lesson 3-11 Use the Excel DATE function to offset days, months and years.

 

I have attached an example workbook showing how you can convert the number 20170514 into a date serial number that Excel will understand and allow you to add days to:

 

Attached File  DateConversion.xlsx   9.62KB   14 downloads

DateConversion.png

 

If you're not familiar with the concept of date serial numbers, you can find an explanation in: Expert Skills Lesson 3-7 Understand Excel date serial numbers and custom date formats.

 

I hope this is the solution you need, but please feel free to reply again if you need any more help with this.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users