If I understand your question correctly, you can use the DAY function. Here's a quick example. If cell A1 contains your date, then the formula =DAY(A1) gives you the day number.
+Jaditza Lopez. Hi Jaditza. To build a bi-weekly (fortnightly) date sequence, enter the first 2 dates into 2 sequential cells (e.g. 1-May-16 and 15-May-16), then select both cells and autofill to extend the sequence. To show day names (which should be the same), simply reformat. Hope that helps. Thanks for your question.
Unless i am doing this wrong, it's showing me the first date i put repeatedly. Im trying to make the dates change automatically on a time card so i wont have to continue to manually do it, every pay period.
+Jaditza Lopez. Hello again. In this case you need to use a formula. In the first cell, type =TODAY() to enter the current date (or manually type in the date you want to start with). Assuming your first cell is A1, then in the second cell, type =A1+14 to calculate the date 14 days from the first date. You can then autofill the second cell as far as you need. Dates are stored as numbers in Excel. Day 1 is 1-Jan-1900. Day 2 is 2-Jan-1900 etc. You don't need to know what the numbers are but it makes date calculations easy because you just add or subtract numbers from the date you are working with. Does that answer your question Jaditza?
+Jaditza Lopez. The formulas control the dates. To show the days of the week for each date, you simply change the formatting. Select the cells, then right-click and choose Format Cells. In the dialog box, choose a long date format from the Date category (e.g. Tuesday, 26 April 2016) or choose the CUSTOM category and set your own. Custom date formats work as follows: - d, dd, ddd or dddd show the day as 1, 01, Tue or Tuesday - m, mm, mmm or mmmm show the month as 1, 01, Apr or April - yy or yyyy show the year as 16 or 2016 - punctuation is displayed as typed, e.g. comma, period, brackets, dashes etc. Some example custom date formats: - dd-mmm-yy displays the date as 26-Apr-16 - ddd, d/m/yy displays the date as Tue, 26/4/16 - dddd, d mmmm yyyy displays the date as Tuesday, 26 April 2016 - dddd only displays the day name, i.e. Tuesday
+RAHUL SHROFF Hi Rahul and sorry for the late reply - I have been off the radar for a while! To generate your list, do the following: 1) Type 1/1/16 into a cell. 2) Select the cell, hover over the autofill handle (bottom right corner of cell) and click-and-drag across to the next cell. 3) Click the Autofill Options smart icon and choose Fill Months. 4) Select both cells, then right-click and choose Format Cells. 5) Select the Number tab (if necessary), then choose 'Custom' from the list. 6) In the 'Type' box, delete the current contents and type 'mmm yy' (without the quotes), then press Enter or click OK. 7) With both cells selected, hover over the Autofill handle again (bottom-right corner of cell) and click-and-drag across the worksheet until the screen tip says "Dec 21'. 8) Job done! I hope that helps. Thanks for your question.
You cannot do this with the autofill options, but it's easy with a formula. If A1 contains the start date, e.g. March 23, 2022 (Monday), then in A2 type the formula =IF(WEEKDAY(A1)=2, A1+1, A1+6) then copy that down. WEEKDAY returns a value between 1 and 7 where 1 = Sunday and 7 - Saturday.
I like this straight to the point. Thanks for taking the time to help few people :D
No worries. You're welcome.
Very clean and straight forward tutorial. Very easy to follow.
Thank you Ammar
Excellent lesson!
Thank you jns. I'm glad you liked it.
Thank you! This was hugely helpful in building my planner pages
Thanks Ronda. I really appreciate your comments.
thank you, helped a lot.
Thanks Clive. I'm glad it helped.
Thanks, very good info.
Thanks Sushil. I'm glad you found it useful.
Thanks a lot for this excellent job
And thank you for your kind comment.
How would I be able to add a number to a date or just assign a number 1 to 30 to a specific date like 11/02/16 mm/dd/yy to show just the number 2
If I understand your question correctly, you can use the DAY function. Here's a quick example. If cell A1 contains your date, then the formula =DAY(A1) gives you the day number.
How can i repeat days of the week for a biweekly time card?
+Jaditza Lopez. Hi Jaditza. To build a bi-weekly (fortnightly) date sequence, enter the first 2 dates into 2 sequential cells (e.g. 1-May-16 and 15-May-16), then select both cells and autofill to extend the sequence. To show day names (which should be the same), simply reformat. Hope that helps. Thanks for your question.
Unless i am doing this wrong, it's showing me the first date i put repeatedly. Im trying to make the dates change automatically on a time card so i wont have to continue to manually do it, every pay period.
+Jaditza Lopez. Hello again. In this case you need to use a formula. In the first cell, type =TODAY() to enter the current date (or manually type in the date you want to start with). Assuming your first cell is A1, then in the second cell, type =A1+14 to calculate the date 14 days from the first date. You can then autofill the second cell as far as you need.
Dates are stored as numbers in Excel. Day 1 is 1-Jan-1900. Day 2 is 2-Jan-1900 etc. You don't need to know what the numbers are but it makes date calculations easy because you just add or subtract numbers from the date you are working with.
Does that answer your question Jaditza?
+02RIV Now how can I make the days of the week show Monday-Sunday & the dates still automatically change to when the new period starts?
+Jaditza Lopez. The formulas control the dates. To show the days of the week for each date, you simply change the formatting. Select the cells, then right-click and choose Format Cells. In the dialog box, choose a long date format from the Date category (e.g. Tuesday, 26 April 2016) or choose the CUSTOM category and set your own.
Custom date formats work as follows:
- d, dd, ddd or dddd show the day as 1, 01, Tue or Tuesday
- m, mm, mmm or mmmm show the month as 1, 01, Apr or April
- yy or yyyy show the year as 16 or 2016
- punctuation is displayed as typed, e.g. comma, period, brackets, dashes etc.
Some example custom date formats:
- dd-mmm-yy displays the date as 26-Apr-16
- ddd, d/m/yy displays the date as Tue, 26/4/16
- dddd, d mmmm yyyy displays the date as Tuesday, 26 April 2016
- dddd only displays the day name, i.e. Tuesday
How can I make a Month & Year series starting Jan,`16 and that ends in Dec,`21 in a row???
+RAHUL SHROFF Hi Rahul and sorry for the late reply - I have been off the radar for a while! To generate your list, do the following:
1) Type 1/1/16 into a cell.
2) Select the cell, hover over the autofill handle (bottom right corner of cell) and click-and-drag across to the next cell.
3) Click the Autofill Options smart icon and choose Fill Months.
4) Select both cells, then right-click and choose Format Cells.
5) Select the Number tab (if necessary), then choose 'Custom' from the list.
6) In the 'Type' box, delete the current contents and type 'mmm yy' (without the quotes), then press Enter or click OK.
7) With both cells selected, hover over the Autofill handle again (bottom-right corner of cell) and click-and-drag across the worksheet until the screen tip says "Dec 21'.
8) Job done!
I hope that helps. Thanks for your question.
How to create sequence for 2 days of the week but not weekend? E.g. Mon and Tue or Wed and Friday
You cannot do this with the autofill options, but it's easy with a formula.
If A1 contains the start date, e.g. March 23, 2022 (Monday), then in A2 type the formula =IF(WEEKDAY(A1)=2, A1+1, A1+6) then copy that down.
WEEKDAY returns a value between 1 and 7 where 1 = Sunday and 7 - Saturday.
@@JasonMorrell It works! Thank you so much!