Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

Date Am and PM but need the date to change [Solved/Closed]

Report
-
Posts
2717
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
April 1, 2021
 -
Hello,

I have 12 workbooks, what I am trying to figure out is I have the date and time in one cell. But I has it 1/1/2020 00:00 then them next line is 1/1/2020 12:00. Is there a way to have this auto populate to for the month? If not is there a way to change the month change. So if I put in 01/01/2020 and the next workbook is 02/01/2020 all I would have to do is add the 02/01/2020 to the first line and it would auto populate the rest?


System Configuration: Windows / Edge 17.17134

2 replies

Answer 1 / 2
Posts
2717
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
April 1, 2021
 458
Hi JeepTruck,

I think you are confused with the terminology of workbooks and worksheets. Workbooks are the Excel files and worksheets are the tabs within an Excel file.

So what I think you are trying to do is put 1 date in cell A1 of the first sheet and the other 11 automatically pick that date and changes the month, so that all 12 sheets represent a month of the year.

If I'm right in my assumption, then try this:

Fill out the date on your first sheet, then use the following formula on sheet 2:
=DAY(Sheet1!A1) & "-" & MONTH(Sheet1!A1)+1 & "-" & YEAR(Sheet1!A1)

Then for sheet 3 you can either change the sheet reference Sheet1! to Sheet2!
OR
change +1 to +2 in the middle of the formula.

Best regards,
Trowa
2
Thank you
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Answer 2 / 2
Posts
4
Registration date
Monday December 23, 2019
Status
Member
Last seen
January 14, 2020
Excel contains the feature of any data auto-filling. To perform that, you need to write the first value in the right format(f.e. 01.01.2020) after that you should pull the small square(as at the screen) down, left, up, or right. If you'll pull from 01.01.2020 to 5 positions right, you'll get the automated filled data in the selected area from 01.01.2020 to 06.01.2020. But if you would like to make auto-filling of data more specific, you can code custom macros using VBA(is inbuilt).
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!