Until recently, there has been just one easy way to generate dates in Excel - the AutoFill feature. The introduction of the new dynamic array SEQUENCE function has made it possible to make a series of dates with a formula too. This tutorial takes an in-depth look at both methods so that you can choose the one that works best for you. Continue reading
Comments page 3. Total comments: 294
I need to make a list of 2 columns, first column starts with 9/17/23, and goes for 1 year, but each date is 6 weeks apart. I see how I can do it daily, monthly, etc, but not 6 weeks Thanks
Hi! Use the recommendations from the article above as well as these instructions: How to add and subtract dates, days, weeks, months and years in Excel.
=SEQUENCE(20,1,DATE(2023,9,17),42)
Hi there,
Do you know how I would go about creating dates for a 4 on and 4 off shift pattern?
For example:
Start Date 28/07/2023
28/07/2023
29/07/2023
30/07/2023
31/07/2023
05/08/2023
06/08/2023
07/08/2023
08/08/2023
13/08/2023
14/08/2023
15/08/2023
16/08/2023
Hope this makes sense!
Any help would be much appreciated :)
Hi! If I got you right, the formula below will help you with your task:
=(CEILING(SEQUENCE(20,1,1,1)/4,1)-1)*4+SEQUENCE(20,1,0,1)+A2
A2 = 28/07/2023
For more information, read: SEQUENCE function in Excel - auto generate number series.
Hi
I'm trying to create a registers using excel where the class is only seen 2 days a week
For example I would like it to read
Monday 04/09/2023
Wednesday 06/09/2023
Monday 11/09/2023
Wednesday 13/09/2023
etc
Is this possible?
Hi!
A1 = 04/09/2023
A2 = A1+2
A3 = A2+5
Select A2 and A3 and drag down the column. Read more: How to use AutoFill in Excel - all fill handle options.
I am trying to create a series of dates which I want to sort into a larger collection of dates.
Surely there is a simple way to do this!
It appears if I use the SEQUENCE formula to create the list that I am not able to sort this list.
When I do a right click on the Fill handle of a cell that has a date from which I wish to create a date list, I am not able to select any options - they are all greyed out.
It used to be that you could start a date list with a minimum of three dates and then use the Fill handle to extend the list into the future but now all that seems to happen when I do this is a replication of the dates in the list, not a continuation of the list.
Any other suggestions?
Hi! If you have created a sequence of dates using a SEQUENCE formula, you will only be able to copy and sort them if you convert that formula to values. I recommend this instruction: How to quickly convert formulas to values in Excel.
I'm trying to fill a sequence for the end of every quarter starting on 9-23:
09-23
12-23
03-24
06-24
09-24
12-24
03-25...
Hi! Use the EOMONTH function to create a sequence of dates with an interval of 3 months. Use the TEXT function to get these dates in the format "mm-yyy".
Check the formula below, it should work for you:
=TEXT(EOMONTH(DATE(2023,9,1),SEQUENCE(20,,3,3)-3),"mm-yy")
Abraham, Mampatta Israel
8/6/2020
8/13/2020
Abram, Eulis
4/16/2021
5/11/2021
Abreu, Nekita M
4/23/2020
5/5/2021
Acharya, Ajay Madhusudan
9/4/2020
Adams, Anne K
8/31/2020
Adams, Elke G
4/16/2021
I have 10000 data in one column, and I need name in one column and in other dates mentioned underneath
Based on your description, it is hard to completely understand your task. If you want to transform your data into 3 columns, I don't think it is possible. Your data does not have the same pattern (two dates and one date).
Hi. How can I create a sequence of dates of workdays like Monday, Wednesday, Friday or Monday to Friday. I want the formula to give me the dates of a month for Monday Wednesday Friday each week. For example for August 2023, I want the formula to give me the below dates; 2nd, 4th,7th,9th,11th,14th,16th,18th,21th,23th,25th,28th,30th.
Is it poosibe to get that data with excel?
Hi! To create a sequence of working days, try using the WORKDAY.INTL function.
Use argument "Weekend string", as described in this article.
=WORKDAY.INTL(A1,1,"0101011")
A1 - start date (1 Aug 2023)
Try to enter the following formula in cell A2 and then copy it down along the column.
Two dates in excel cell like 12-01-2005 & 15-02-2008
we want in another cells as result 31-03-2005,30-06-2005,30-09-2005,31-12-2005,31-03-2006,30-06-2006,30-09-2006,31-12-2006,31-03-2007,30-06-2007,30-09-2007,31-12-2007,29-02-2008,
How we can do with macro.
Hi! You can create a sequence of dates at 3-month intervals using the EOMONTH function. For example,
=EOMONTH(A2,3)
I have a table with a calendar that I want to populate with a 'check mark' on specific days in a sequence. Essentially I want to take a start date for a task and a frequency for said task, and have it put a check mark on the dates at regular intervals. Is there a simple formula that would generate a sequence for me?
As we have written many times on this blog, a formula cannot change the value in another cell. Use VBA.
I'm trying to repeat a sequence after every 21 rows, like first 21 rows have "7" then next 21 rows will have "8" again next 21 rows will have "9" and this will continue. how this can be achieved.
Hi! You can make a sequence of repeating values using the CEILING function. Try this formula:
=CEILING(SEQUENCE(100,1,1,1)/21,1)+6
I'm trying to repeat a date sequence after every 7 rows, like first 7 rows have date 6-8-23 then next 7 rows will have 7-8-23 and this will continue. how this can be achieved.
Hi! To create a sequence with repeating values, use the CEILING function together with SEQUENCE. Add these values to the start date. Try this formula:
=CEILING(SEQUENCE(49,1,1,1)/7,1)+DATE(2023,8,5)
Hi
I have a financial year calendar of July 2023 to June 2024 (07/01/23 - 06/30/24) and I need to sequentially create a 52 week calendar in excel, which will be Monday to Sunday, as follows:
07/01/23 - 07/02/23
07/03/23 - 07/09/23
07/10/23 - 07/16/23
07/17/23 - 07/23/23
07/24/23 - 07/30/23
so on and and so forth. Can you please help formulate the same?
Thanks in advance!
Hi! Your request goes beyond the advice we provide on this blog. This is a complex solution that cannot be found with a single formula. If you have a specific question about the operation of a function or formula, I will try to answer it.
I need to make sequence like this as given below, Please help me on this
01 - 01/07/2023 00:00
01 - 01/07/2023 00:10
.
.
.
01 - 01/07/2023 23:50
01 - 02/07/2023 00:00
02 - 01/07/2023 00:00
02 - 01/07/2023 00:10
.
.
.
112 - 02/07/2023 00:00
Hi! Add 10 minutes to the first value and copy the formula down the column. Read more: Adding or subtracting hours, minutes and seconds to a time. For example,
=A1+TIME(0,10,0)
hi would like to insertb6 empty cells after each week.
Hi,
I have data which has been recorded every 15 minutes from between 4/1/2020-27/4/2020.
I need to code it using R so require the layout of this spreadsheet to require 2 separate columns of date and time.
I have added the time column but I need the date to essentially remain the same for each 24 hour period and increase by 1.
e.g.
4/1/2020 | 00:00:00
4/1/2020 | 00:15:00
4/1/2020 | 00:30:00
...
...
...
4/1/2020 | 23:45:00
5/1/2020 | 00:00:00
5/1/2020 | 00:15:00
and so on
Could you help with this?
Thanks
Hi! If I understand your task correctly, try the following formula for cell A2:
=IF(B2<B1,A1+1,A1)
That works perfectly - thank you!
I am hoping to create a excel spreadsheet that has the days of the month populate across column 5 and change monthly based on the number of days in that month I also would like to highlight every row that is a sunday hoping I can get assistance with this
Hi! If I got you right, the formula below will help you with your task:
=EOMONTH(TODAY(),-1) + SEQUENCE(DAY(EOMONTH(TODAY(),0)),1,1,1)
You can learn more about EOMONTH function in Excel in this article on our blog
Hello, I am currently working on my study plan, and willing to share it to my friends. I would like to be able to auto change the date of each topic based on the individual's chosen number of study sessions per day.
For example:
In cell A1, there is an option to choose the numbers: 1 or 2 or 3 - study sessions per day.
If I choose 1 session per day this is what the column looks like:
column B
July 5, 2023
July 6, 2023
July 7, 2023
and so on
For 2 sessions per day
same column B
July 5, 2023
July 5, 2023
July 6, 2023
July 6, 2023
Again, I would like to be able to auto sequence change the whole column based on the chosen session in cell A1.
Thank you, I really appreciate your help! Good day!
Hi! If I understand your task correctly, the following formula should work for you:
=B1+CEILING(SEQUENCE(30,1,1,1)/A1,1)
B1 - start date
I'm looking for a way, starting from 1/1/23, to have excel pull dates through the rest of the year in the following pattern: 2,3,6,7,8,11,12 (these numbers come from a 1-14 or two week interval). So for example it would return:
2- 1/2/23
3- 1/3/23
6- 1/6/23
7- 1/7/23
8- 1/8/23
11- 1/11/23
12- 1/12/23
2- 1/16/23
3- 1/17/23
6- 1/20/23
7- 1/21/23
8- 1/22/23
11- 1/25/23
12- 1/26/23
2- 1/30/23
3- 1/31/23
Hi! Unfortunately, I don't see any logical sequence in your numbers that can be written as a formula. If you see logic in these numbers, tell me.
I've tryed to use the sequence creatio formula =WORKDAY(B2-1, SEQUENCE(B1)) and it works fine but I can't the column generenated within a vlookup array it simple doesn't read it as dates
Hi! If cell B2 contains the date, check what value you are searching for with VLOOKUP. It may be a text.
Hi. I am scouring the internet trying to find a way to store ranges of dates in excel, that automatically adjust based on the current month. So I have a list of events, and each event runs from X day to Y day. Some go into the following month. For example, Event A runs from the 12th of the month to the 08th of the following month. Is there a formula that will automatically update this for me based on today's date, in excel?
Hi!
To create a date, use the DATE function. To get the next month's date, use the EOMONTH function.
Try this formulas:
=DATE(YEAR(TODAY()),MONTH(TODAY()),12)
=DATE(YEAR(TODAY()),MONTH(EOMONTH(TODAY(),1)),8)
if i put a date in a cell, and if the date a sunday i have to display and "S" with the date in same cell
Hi! You can only add a character to a cell with a date by using a VBA macro. And then you will get text in the cell, not the date. To show the day of the week, use a custom date format. For example: ddd, mmmm dd, yyyy
I am wanting to have the next date at the top and the last date at the bottom. Can anyone help please
Hi! If you want to get a descending sequence of dates from a future date to a past date, use a negative number in the [step] argument of the SEQUENCE function. For example,
=SEQUENCE(10, 1, "8/1/2020", -1)
Hope this is what you need.
Greetings,
My issue is probably going to be easy and I just am over looking how. I am trying to create a month long series of
workbook pages with page 1 being the "1st" next "2nd" etc.
Id like the entire date (mm/dd/yy) for each page to be in C1 of every page auto advancing. What am I missing to do so?
Thank you in advance.
Hi! Excel formulas cannot create and rename your workbook sheets. This is possible with a VBA macro.
Hi,
I might have missed it, but I am trying to create a list of dates that starts on the 1/1/2023 and ends today, and that updates the "today" avery time the spreadsheet is open (ie incrementally adds the days as the year goes by). Is that possible?
Hi! Determine the number of the day in the current year and create a sequence of dates as described in the article above
If I got you right, the formula below will help you with your task:
=SEQUENCE(TODAY()-EOMONTH(TODAY(),-MONTH(TODAY())),1,EOMONTH(TODAY(),-MONTH(TODAY()))+1,1)
Thank you it worked great! I am not sure I understand the logic behind this sequence but I'll keep trying :)
I am trying to create a column sequence of dates by day among a few years. However, whenever I use the fill function to lets say pass from November 30th to the 1st of December, it automatically starts December with the 3rd and not the first. For instance:
11/22/2007
11/23/2007
11/24/2007
11/25/2007
11/26/2007
11/27/2007
11/28/2007
11/29/2007
11/30/2007
12/3/2007
Note how the last one started form the 3rd
Hi! Unfortunately, it was not successful to reproduce your problem. Try using formulas to create a sequence of dates. There is all the information you need above in this article.
Hi there,
I would like to create a series of dates on columns across the top of a spreadsheet as they are reading infro from different tabs.
e.g,
3 Apr 23 3 Apr 23 3 Apr 23 10 Apr 23 10 Apr 23 10 Apr 23 17 Apr 23 17 Apr 23 17 Apr 23 and so on
Therefore repeating the date 3 times across the top then going up by one week and repeating the same pattern I hop that makes sense
Thanks in advance for your help.
Hi! Add to the starting date the number of days in the sequence created with the SEQUENCE function. Try this formula:
=DATE(2023,3,27)+CEILING(SEQUENCE(45,1,1,1)/3,1)*7
Hello sir,
I need your help regarding daily attendance sheet monthly report .for example if "Friday" is weekend day and there are 4 or 5 "Friday" in a month. with daily attendance entry when ever Friday come automatically count as a payable day in salary with daily present and absent for salary calculation.
in my excel sheet I have a problem there are 4 "Friday" in a month and when I enter even 1 " PRESENT" automatically 4 "Fridays" count as a payable day and result comes 5 day salary.
I'm using this formula (AN14= daily presents)
=IF(AN14=0,0,COUNTIF($F$8:$AJ$8,"Fri")+AN14)
Hi! Unfortunately, this information is not enough to recommend a formula to you. Please provide me with an example of the source data and the expected result.
Hi sir,
For salary calculation i have to use formulas to separate days .. if any employee who works 27 days (30 days a month) means i have to give full salary and if that employee worked 28 days means i have to give full salary +1 day salary ... as like for 30 -3 days .. so which one is suited
Hi! I am not sure I fully understand what you mean. This information is not enough to recommend a formula to you.
I am needing help with a formula that would allow me to have the same date in 'x' amount of rows (for this example lets say 5 rows) and then it changes to the next day for the next 5 rows and so on and so forth. With that being said, I want to only include weekdays (Monday - Friday) as well as any company holidays I may have (Example: July 4th).
Example:
6/30/2023
6/30/2023
6/30/2023
6/30/2023
6/30/2023
*Skips Weekend*
7/3/2023
7/3/2023
7/3/2023
7/3/2023
7/3/2023
*Skips 7/4 Holiday*
7/4/2023
7/4/2023
7/4/2023
7/4/2023
7/4/2023
Continued until 11/1/2023. And if I needed to include additional company holidays besides the one I provided, how would I include that in the formula?
Appreciate the help!
Hi!
To calculate workdays with custom weekends, use WORKDAY.INTL function. For the "days" argument, use a sequence of numbers that can be given by the SEQUENCE function.
For example:
=WORKDAY.INTL(D1,SEQUENCE(50,1,1,1),1,E1:E5)
For more information, please visit: Calculating weekdays in Excel - WORKDAY and NETWORKDAYS functions.
How to create excel table to auto fill the dates in one column with the month and Year given in a cell as input and how to high light the weekend days
Example
Month and Year 03/2023
Dates
01
02
03
04
05
06
07
Have you tried the ways to create the date sequence described above in this article? If that doesn't work for you, describe in detail what result you want to get.
To create a date in an adjacent cell based on these values, you can use the formula
=DATEVALUE(A2&"/"&"03/23")
Read more: How to convert text to date and number to date in Excel
I want to know is it possible to autofill date in a column of particular month, when the month is provided by the user.
Hi!
All the necessary information is in the article above.
The formula below will do the trick for you:
=DATE(YEAR(TODAY()),M1,SEQUENCE(DAY(EOMONTH(DATE(2023,M1,1),0))))
The DAY and EOMONTH functions help you get the number of days in a month.
Hi! I'm hoping to get some help if possible- I'm trying to create a daily attendance log with the date at the top right corner(dddd, mmmm dd. yyyy) that auto-updates to the next workday on print?? Is this even possible? Specifically: cell is currently set to Monday, April 03, 2023, and on Print it would auto-update to Tuesday, April 04, 2023, and so on for the month.
Maybe I'm looking for a function that doesn't exist, but it'd sure be nice to be able to go CTRL+P at 30 "copies" and have them all automatically be from Monday-Friday for the month, instead of having to type the date in for each day.
I'm really sorry, we cannot help you with this.
How can i have a fixed date in a cell change every 28 days within that same cell.
Example Cell A1 '12/02/23' change to '12/03/23' on that date. Then again change to '09/04/23' on that date, and so on.
Thanks in advance
Hi, I am trying to get a formula where i can write the date in one cell then have cells three rows below auto populate the week entirely.
Hi!
Maybe this article will be helpful: How to add and subtract dates, days, weeks, months and years in Excel.
I am trying for Date Sequence With regular interval of blank five columns but Not Working Any Suggestions ?
Hi!
This looks completely incomprehensible, but the data sequence cannot create empty values.
like for example i am trying for this.....
March 2, 2023
March 3,2023
March 4,2023
in this way i am trying to do if any thing possible ?
Hi!
If I understand correctly, write the date in the first cell. Select that cell and the empty cell below it. Hover the mouse cursor over a small square at the lower right-hand corner of the selected range. Hold and drag the fill handle down the column.
Hello,
can anyone help me with a formula for the following:
Tuesday, February 28, 2023 AM
Tuesday, February 28, 2023 PM
Wednesday, March 01, 2023 AM
Wednesday, March 01, 2023 PM
Thursday, March 02, 2023 AM
Thursday, March 02, 2023 PM
Friday, March 03, 2023 AM
Friday, March 03, 2023 PM
Saturday, March 04, 2023 AM
Saturday, March 04, 2023 PM
Sunday, March 05, 2023 AM
Sunday, March 05, 2023 PM
Monday, March 06, 2023 AM
Monday, March 06, 2023 PM
and etc....
Thank you in advance
Hello!
Write in the first cell the starting date and time, for example, 1.03.2023 01:00:00 In the next cell add to this value 0.5: A1+0.5 Copy down the column. Use a custom date format
dddd, mmmm dd, yyyy AM/PM
I hope my advice will help you solve your task.
I am trying to produce a spreadsheet with a month per sheet for the financial year, each sheet having headers for week commencing with Monday dates. I want to be able to enter the first Monday of the financial year and all the dates automatically enter into the headers. Initial this seemed simple enough with a cell+7 formula. However, as I want to be able to use this same spreadsheet template year after year, this does not allow for the fact that some years a month may have four Mondays, but five Monday a subsequent year. I worked out a formula to only enter a date in the final column if it was before the 1st of the following month, however this only seems to work if there is a year in the date, meaning I would have to change the formulas each financial year (which I don't want to have to do, just change the date of the first Monday). Is there a way to do this?
Hi!
Sorry, it's not quite clear what you are trying to achieve. Give an example of the sequence of dates you want to get.
Till now, the autofill of sequential dates has worked, but suddenly it doesn't anymore. Is there a setting or something that blocks the autofill feature?
Yes I have the same issue since a couple of weeks, haven been searching everywhere. It's NOT the options as you read everywhere... but no autofill anymore for numbers and dates, I presume since one of the recent updates ?
I want to fill a row with the days of week. Cell 1 1/1/2023-1/7/2023 then rack cell on row after that the next calendar week... 1/8/2023-1/14/2023,1/15/2023-1/21/2023, etc
Michelle,
I'm looking to do something similar but couldn't accomplish using the options described here. Were you successful? Thanks!
Hi!
Have you tried the ways described in this blog post? If they don’t work for you, then please describe your task in detail, I’ll try to suggest a solution.
I would like to know the steps to create Date sequence horizontally excluding the weekends ( Saturday and Sunday )
I was curious if someone knew how to make a date sequence formula skipping five rows and continuing the sequence? I am trying to make a calendar with four cells for each day to input data.
Is there a way to type in a start date (example: 1/2/23) and an end date (example: 1/24/23) and have excel automatically fill in the dates between the start/end in rows below? This is for a time sheet with variable time periods, where I'd like the employees to be able to enter in the start/end dates of the time period, but not have to write in each date, or have to click and drag down the column using the "B2+1" formula. Basically is it possible for excel to expand dates in "1/2/23" format, from a start and end date, that is a variable length of time (not each time period is the same number of days long). Thanks so much!
Hi!
All the necessary information is in the article above.
D1- start date. D2 - end date. Try this formula:
=SEQUENCE(D2-D1+1,1,D1,1)
Hello,
Looking to create a table where the same date is listed for that week and then adds 7 days for the next set - I.E
12/14/2022
12/14/2022
12/14/2022
12/14/2022
12/14/2022
12/21/2022
12/21/2022
12/21/2022
12/21/2022
12/21/2022
12/28/2022
12/28/2022
12/28/2022
12/28/2022
12/28/2022
Hello!
Set start date with DATE function and create sequence
=(CEILING(SEQUENCE(45,1,1,1)/7,1)-1)*7+DATE(2022,12,14)
Hi,
Looking to create a table where the same date is listed 3 times for one day, then sequence for 1 year
01/01/2024
01/01/2024
01/01/2024
02/02/2024
02/02/2024
02/02/2024
Hi! That question has already been asked here.
Hi Guys,
Would anyone know how to autofill date on every let's say 50th row? So if I have 19/12/2022 in A1 and want to autofill 20/12/2022 in A50. Is there an easy way of doing this? I know you can drag the date down with a mouse and select a row on which you want to autofill the next day but I would have to do it 365 times.
Any feedback much appreciated.
Thank you.
Hello!
Create a start date using the DATE function and add a sequence of numbers. If I understand your task correctly, try the following formula:
=DATE(2022,12,19) + CEILING(SEQUENCE(365,1,1,1)/49,1)-1
So I have a set of dates for Mondays.
Is it possible to change / update the first cell sequentially like every 4 weeks?
For example:
Mon, 21 Nov 22
Mon, 28 Nov 22 (+7)
Mon, 5 Dec 22 (+14)
Mon, 12 Dec 22 (+21)
I'm trying to automatically change Mon, 21 Nov 22 every 28 days
Hi!
Use SEQUENCE function to create sequence of dates:
=DATE(2022,11,21) + SEQUENCE(10,,0,7)
Sorry, I guess I wasn't clear. To give context, I'm using =Today()-Weekday(Today())+2 to determine the Monday for the current week.
So for this week it is Mon, 21 Nov 22
I'm trying to auto update this cell because a cycle takes 4 weeks or 28 days to renew. If I leave it as is, the date will update weekly and that will mess with the assignments for the other set dates since the cycle hasn't been completed.
Assume A,B,C,D are people. Each week there are tasks assigned only for column 1 (they mentor a specific team member in column B).
A,B,C - D - Mon, 21 Nov 22
A,B,D - C - Mon, 28 Nov 22
A,C,D - B - Mon, 5 Dec 22
B,C,D - A - Mon, 12 Dec 22
I hope this makes sense, if its not possible it's fine. Was worth asking / trying. Thanks
Hi!
Unfortunately, I don't understand what you want to do.
Basically auto update a date every 28 days.
I need to create a sequence for attendance period from 26th of current month to 25th of subsequent month. I am mentioning from date in cell "A2", then providing equation in cell "B5" as "=A2" and setting custom format to "DD". Cell "C5" to "AF5" equation is set "+1". Let's say A2 is 26/10/2022, hence B5 is 26 and AF5 is 25. if I change date in A2 to 26/11/2022, AF5 will be 26 in this case. Since it crosses attendance period of 25th, AF5 should become blank. Please guide what excel formula will help this scenario.
Hello!
Get the day from a date using the DAY function. Try this formula in cell C5
=IFERROR(IF(DAY(B5+1)=25,"",B5+1),"")
Hello
I would like to know how I can create a formula with a sequence of 4 days in and 4 days out, including weekends. Thank you
Hi!
If I got you right, the formula below will help you with your task:
=WORKDAY(A1-1, TRUNC(MOD((SEQUENCE(100,1,1,1)-1)/1,4)+1) + (CEILING(SEQUENCE(100,1,1,1)/2,2)-2)*4)
Hello!
If you can help me, please. I have to generate for every month a sheet with 19-25 (25 week days and 19 in weekends) intervals per every day, from half to half a hour, start from 8:00 AM and end at 8:00 PM in week days and in weekend start from 9:00 AM and end at 6:00 PM.
EX: 10/9/22 is Friday and 10/10/2022 is Saturday
10/9/2022 12:00 PM
10/9/2022 12:30 PM
10/9/2022 1:00 PM
10/9/2022 1:30 PM
10/9/2022 2:00 PM
10/9/2022 2:30 PM
10/9/2022 3:00 PM
10/9/2022 3:30 PM
10/9/2022 4:00 PM
10/9/2022 4:30 PM
10/9/2022 5:00 PM
10/9/2022 5:30 PM
10/9/2022 6:00 PM
10/9/2022 6:30 PM
10/9/2022 7:00 PM
10/9/2022 7:30 PM
10/9/2022 8:00 PM
10/10/2022 9:00 AM
10/10/2022 9:30 AM
10/10/2022 10:00 AM
10/10/2022 10:30 AM
10/10/2022 11:00 AM
10/10/2022 11:30 AM
10/10/2022 12:00 PM
10/10/2022 12:30 PM
10/10/2022 1:00 PM
10/10/2022 1:30 PM
10/10/2022 2:00 PM
10/10/2022 2:30 PM
10/10/2022 3:00 PM
10/10/2022 3:30 PM
10/10/2022 4:00 PM
10/10/2022 4:30 PM
10/10/2022 5:00 PM
10/10/2022 5:30 PM
10/10/2022 6:00 PM
I hope it's posibile :(
Thank you verry much!
Hi! :)
I've tyried something, that works, but not completly well. I cant SEQUENCE the SEQUENCE :)) (if it's posible). If i modify the start date all is good, but i need to populate the sheet with all days :(, not one by one.
My formula " =IF(WEEKDAY(R1,2)<6,SEQUENCE(25,1,DATE(YEAR(R1), MONTH(R1), DAY(R1)),0)+SEQUENCE(25,1,TIME(8,0,0),1/48),SEQUENCE(19,1,DATE(YEAR(R1), MONTH(R1), DAY(R1)),0)+SEQUENCE(19,1,TIME(9,0,0),1/48)) "
in R1 is the date 10/1/2022
Thank you.
Hi!
Sorry, it's not quite clear what you are trying to achieve.
Hey!
Sorry for that :))..
Short story. On A colomn I need to generate for every weekday a set of 25 interval per day (until the end of the world :)) ).
Like example, for day 3 Aug 2022 (weekday,25 intervals 8AM-8PM):
10/3/2022 8:00 AM
10/3/2022 8:30 AM
10/3/2022 9:00 AM
10/3/2022 9:30 AM
10/3/2022 10:00 AM
10/3/2022 10:30 AM
10/3/2022 11:00 AM
10/3/2022 11:30 AM
10/3/2022 12:00 PM
10/3/2022 12:30 PM
10/3/2022 1:00 PM
10/3/2022 1:30 PM
10/3/2022 2:00 PM
10/3/2022 2:30 PM
10/3/2022 3:00 PM
10/3/2022 3:30 PM
10/3/2022 4:00 PM
10/3/2022 4:30 PM
10/3/2022 5:00 PM
10/3/2022 5:30 PM
10/3/2022 6:00 PM
10/3/2022 6:30 PM
10/3/2022 7:00 PM
10/3/2022 7:30 PM
10/3/2022 8:00 PM
10/4/2022 8:00 AM
10/4/2022 8:30 AM
10/4/2022 9:00 AM
10/4/2022 9:30 AM
10/4/2022 10:00 AM
10/4/2022 10:30 AM
...same pattern for 4,5,6,7,8 Aug...
....then at 9 and 10 Aug when is weekend i need to generate only 19 intervals (from 9AM to 6PM)
10/9/2022 9:00 AM
10/9/2022 9:30 AM
10/9/2022 10:00 AM
10/9/2022 10:30 AM
10/9/2022 11:00 AM
....
10/9/2022 4:30 PM
10/9/2022 5:00 PM
10/9/2022 5:30 PM
10/9/2022 6:00 PM
...then for 10 Aug...
..then 11 Aug...
untill the end of time :D
Thank you.
If you can, in a new excel put this on A1: =IF(WEEKDAY(R1,2)<6,SEQUENCE(25,1,DATE(YEAR(R1), MONTH(R1), DAY(R1)),0)+SEQUENCE(25,1,TIME(8,0,0),1/48),SEQUENCE(19,1,DATE(YEAR(R1), MONTH(R1), DAY(R1)),0)+SEQUENCE(19,1,TIME(9,0,0),1/48))
And the date in R1 (format m/dd/yyyy)
This will populate A1 to A25 or A19(depend if is weekday or weekend), but i need this to the down end of the colomn A, for each day :D. Practicaly i need to sequence the sequence :))....
Sorry for bother.
Hello!
I'm really sorry, we cannot help you with this issue.
Hi, i wanna ask if a database as > | 28-Apr-22 | 29-Apr22| 2 | = |[from date]|[to date]|[number of day]|, can we seperate the date into 2 database in excel?
Hi!
If I understood the problem correctly, this article might help you: How to split text string in Excel by comma, space, character or mask.
what i do for get sequence of two numbers
exp.
1-2
3-4
5-6
7-8
Hi!
Please re-check the article above since it covers your case. Use the & operator to concatenate values.
=SEQUENCE(50,1,1,2)&"-"&SEQUENCE(50,1,2,2)
Hello! I was wondering if you would be able to help me with a date sequence question. I have also read your sequence post and was not able to find the answer.
I am trying to use a filter function (FILTER(B:B,(B:B>=$L$2)*(B:B<=$M$2),"No data")) that tells me dates I have within a set of data. The problem I am having is that I did not write every date (there are too many) so I wrote the start and end date like the following:
C D
Saturday, December 1, 2018 Friday, March 1, 2019
Monday, March 4, 2019 Friday, March 8, 2019
Monday, March 11, 2019 Friday, March 15, 2019
Monday, March 18, 2019 Friday, March 22, 2019
Monday, March 25, 2019 Tuesday, March 26, 2019
Sunday, April 14, 2019 Thursday, May 30, 2019
Saturday, June 1, 2019 Tuesday, March 31, 2020
(This is in date format, not text)
I wanted it to recognize that these are ranges so that I can execute the FILTER function as planned.
I tried creating a sequence function (C2+SEQUENCE(1+D2-C2,,0)) but cannot drag it for the rest of the rows because of a spill error.
I was wondering if you would be able to provide me with a sequence function I can use that will allow me to infinitely sequence the dates within the range in a single column for each row without the spill error, or if you can help me figure out another way to do a filter function for dates when you only have the the start/end date of ranges.
Thank you!
Hello!
It's not clear from your description what conditions you want to use in FILTER function. What is written in L2 and M2? An example of your data is the C and D columns, which are not in the formula. Your question is not entirely clear, please specify.
I am creating a workbook and my raw data tab has numerous groups names that I need to assign the same DOW. I then want the same number of cells for the next DOW. How can I auto generate this without having to copy and paste.
I.E.
Sunday
Sunday
Sunday
Sunday
Monday
Monday
Monday
Monday
Tuesday
Tuesday
Tuesday
Tuesday
Hello!
Use the SEQUENCE function to create a sequence of dates. Use the TEXT function to convert a date to a day of the week.
=TEXT(WEEKDAY(CEILING(SEQUENCE(60,1,1,1)/4,1),2)+1,"ddddd")
I hope this will help.