+ Reply to Thread
Results 1 to 13 of 13

How to automatically add date based on the previous spreadsheet?

  1. #1
    Registered User
    Join Date
    01-11-2017
    Location
    Chicago, IL
    MS-Off Ver
    2010
    Posts
    80

    How to automatically add date based on the previous spreadsheet?

    I have a large workbook that automatically adds a new spreadsheet every week.

    I'd like to add a date to the first spreadsheet and on subsequent spreadsheets have it automatically reference the date from the previous spreadsheet and add a week so it will reflect the week of the spreadsheet. I don't want to insert the 'current date' because I don't want it to change.

    I thought this would be easy but I'm not finding a formula function that references another sheet dynamically. Can this be done?
    Thanks in advance!

  2. #2
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3,215

    Re: How to automatically add date based on the previous spreadsheet?

    This link helpful of you.
    PHP Code: 
    https://www.youtube.com/watch?v=X4-YqpIloU0 


    atul


    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

  3. #3
    Registered User
    Join Date
    01-11-2017
    Location
    Chicago, IL
    MS-Off Ver
    2010
    Posts
    80

    Re: How to automatically add date based on the previous spreadsheet?

    Thanks Atul. That's the right idea but it's not working for me. Maybe you could help me adjust the formula? I have a sheet for each workweek (ie one is titled MAR16-MAR21, the next is titled MAR23-MAR28, etc. I can reference the name of the prior sheet but not use it as a number to add to the date.

    The date field I want to fill in for each week is in B2.

  4. #4
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: How to automatically add date based on the previous spreadsheet?

    Please try at Sheet1

    =TEXT(DATE(2019,3,15+SHEET()),"mmmd-")&TEXT(DATE(2019,3,15+6+SHEET()),"mmmd")

    Copy formula to the next sheet.
    Change tart date in Blue if needed.

  5. #5
    Registered User
    Join Date
    01-11-2017
    Location
    Chicago, IL
    MS-Off Ver
    2010
    Posts
    80

    Re: How to automatically add date based on the previous spreadsheet?

    Thanks Bo_Ry. I used =TEXT(DATE(2018,12,31+SHEET()),"mmmd-")&TEXT(DATE(2018,12,31+6+SHEET()),"mmmd") but I'm getting an invalid name error. Do I have to add the name of the sheet or anything?

  6. #6
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: How to automatically add date based on the previous spreadsheet?

    SHEET Function only available in Excel 2013

    if your sheet name is Sheet1 , Sheet2 then

    =TEXT(DATE(2018,12,31+RIGHT(SUBSTITUTE(CELL("filename",$A$1),"Sheet",REPT(" ",9)),9)),"mmmd-")&TEXT(DATE(2018,12,31+6+RIGHT(SUBSTITUTE(CELL("filename",$A$1),"Sheet",REPT(" ",9)),9)),"mmmd")

  7. #7
    Registered User
    Join Date
    01-11-2017
    Location
    Chicago, IL
    MS-Off Ver
    2010
    Posts
    80

    Re: How to automatically add date based on the previous spreadsheet?

    Oh. I still have Excel 2010. Is there a way to do it without the SHEET function?

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: How to automatically add date based on the previous spreadsheet?

    Assuming that the date you want to put into cell B2 is also the first date in the tab* name try the following:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    *If the tab name is MAR16-MAR21 then the formula will display 3/16/2020 in cell B2 (dependent on formatting).
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  9. #9
    Registered User
    Join Date
    01-11-2017
    Location
    Chicago, IL
    MS-Off Ver
    2010
    Posts
    80

    Re: How to automatically add date based on the previous spreadsheet?

    THANK YOU JeteMc. You're amazing. That worked!

  10. #10
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: How to automatically add date based on the previous spreadsheet?

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

  11. #11
    Registered User
    Join Date
    01-11-2017
    Location
    Chicago, IL
    MS-Off Ver
    2010
    Posts
    80

    Re: How to automatically add date based on the previous spreadsheet?

    How would I modify this to start with a particular year? I changed the workbook to only start from 7/4/2021 but it's showing the year as 2022, now going into Jan. 2023 (a year ahead). Thanks!

  12. #12
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: How to automatically add date based on the previous spreadsheet?

    Assuming that the sheet tab reads: Jul5-Jul9
    The following would put 7/5/2021 into cell B2:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.

  13. #13
    Registered User
    Join Date
    01-11-2017
    Location
    Chicago, IL
    MS-Off Ver
    2010
    Posts
    80

    Re: How to automatically add date based on the previous spreadsheet?

    That fixed it! Once again JeteMc comes to the rescue!

+ 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: 6
    Last Post: 08-22-2019, 02:49 AM
  2. Replies: 20
    Last Post: 09-15-2017, 12:51 PM
  3. Replies: 0
    Last Post: 02-25-2015, 01:02 PM
  4. Replies: 3
    Last Post: 04-17-2014, 12:21 PM
  5. Replies: 0
    Last Post: 05-30-2013, 07:50 PM
  6. Generating the next number automatically based on a previous cell value
    By hpfeiffer in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-04-2012, 03:04 AM
  7. Macro to automatically fill cells based on previous entries
    By jerinjan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-03-2008, 08:13 AM

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