Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Date Column

0
Friday January 03 2020 Tuesday January 07 2020 Friday January 10 2020 Tuesday January 14 2020 Friday January 17 2020 Tuesday January 21 2020 Friday January 24 2020

Tuesday January 28 2020

How do I create this date progression please

Answer
Discuss

Answers

0
Selected Answer

Try this method:-

[B2] =DATE(2020,1,3)
[B3] = B2+4
[B4] = B2+7

Then copy from B4 as far down as you need. Finally, use Copy/Paste Special > Values to convert the formulas to hard dates (if so needed).

Make sure to format the cells with a custom date format of your choice, such as dddd, dd/mm/yyyy. For this purpose select B2:B4 before copying, right-click and select Format Cells from the context menu. Select the Number tab, Custom and write the mask in the Type field.

Edited Dec 27,2019 ==================================

The formula DATE(2020,1,3) calculates a true date from its three arguments, Year, Month and Day. In Excel, dates are represented by integers. This formula calculates the inter 43833 (the 43833rd day since Jan 1, 1900). You can control the display of this date by setting a different display format (Format Cells). Excel will automatically apply a Short Date format when you ente the formula. So you see perhaps 3/1/2020 or 1-3-2020, depending upon which format you have set as "Short Date" in your Windows settings. You can make the number visible by formatting the cell as Number instead of Date. Or you can apply another date format. And, of course, you can enter another date by either changing the arguments of this formula or by using another method alltogether, just so long as it is a true date - a number. If you enter the date as a text string it may look exactly the same but it can't be used for calculations because text isn't a number.

In the next formula you simply add 4 days to the value you entered in B2. That finds the Tuesday following the Friday in B2, provided you did enter a Friday.

The third formula adds 7 days to the original date, meaning the next Friday if the referenced cell was a Friday, a Tuesday if the referenced cell was a Tuesday, etc. In B4 the referenced cell is B2, two rows above B4. When you copy this formula down one row the referenced cell is also moved one row down. B5 therefore adds 7 days to the Tuesday in B3. B6 adds 7 days to the Friday in B4 etc.

Discuss

Discussion

Hi Variatus I dont fully understand the answer if you could expand the answer please
Thanks
MadMal204 (rep: 2) Dec 26, '19 at 4:47 pm
Thanks that is brilliant my lottery spreadsheet is now working.
Once again thank you very much
MadMal204 (rep: 2) Dec 28, '19 at 5:03 pm
That's great, Mad. Please share the winnings lol:
Variatus (rep: 4889) Dec 28, '19 at 8:32 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login