Jump to content


Photo

Next date from previous sheet to the next sheet

absolute reference date serial numbers

  • Please log in to reply
3 replies to this topic

#1 Abdul Rehman

Abdul Rehman

    Newbie

  • Members
  • Pip
  • 8 posts

Posted 04 January 2017 - 12:31 AM

Hi All
Can someone assist me please on how to add next date in next sheet using the date in previous sheet in the same workbook. For example;
The date in cell A1 of sheet1 is 10/01/2017 so I need to add next date (11/01/2017) in cell A1 of sheet2 in the same workbook.
I tried to do it in cell A1 of sheet2 this way but didn't work;
= date in A1 of sheet1 + 1
I'll really appreciate if someone assist me please.

Thanks in advance



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 862 posts

Posted 04 January 2017 - 12:36 PM

Hi Abdul,

 

If you're not able to add 1 to the date it's most likely because the date is being stored as text instead of being stored as a date serial number. You can check whether this is the case by applying the Number format to the cell and seeing whether the date is shown as a number.

 

You can see how this is done, along with an explanation of date serial numbers, in the video lesson: Expert Skills Lesson 3-7 Understand Excel date serial numbers and custom date formats.

 

The easiest solution to this problem is probably to format the cell as a Date and then manually re-enter the date. It should then be recognized as a date serial number and you should be able to add and subtract from it.

 

I hope this is the solution, but please feel free to reply if you are still having problems.


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 Abdul Rehman

Abdul Rehman

    Newbie

  • Members
  • Pip
  • 8 posts

Posted 13 January 2017 - 05:25 PM

Many thanks Jon. It has worked but I have another problem with the new/next date I get. The problem is when I try to fill it using auto fill handle it gives errors (01/01/1900, #VALUE!).

#4 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 862 posts

Posted 15 January 2017 - 04:05 PM

Hi Abdul,

 

If your formula is looking on another worksheet, filling it down will naturally extract the dates from the same place on the other worksheet. You will probably need to create a different formula after the first one and then fill down the new formula so that it works correctly.

 

It's also possible that you need to use an absolute reference, which you can see more about in the video lesson: Expert Skills Lesson 3-12 Understand absolute and relative cell references.

 

I hope this helps to solve the problem, but please reply if you're still having trouble and attach an example if possible. You can find instructions on how to attach example workbooks here.


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