72 Responses to “Create a date range using excel formula”

1. sireesha Says:
   October 22nd, 2009 at 6:01 pm
   
   

   how can i get the date range as : 01/01/09 - 01/15/09, 01/16/09 - 01/31/09 and so on

2. Oscar Says:
   October 23rd, 2009 at 9:13 am
   
   

   Try this: =TEXT(IF(MOD(COLUMNS($A$1:A1), 2), DATE(2009, ROUND(COLUMNS($A$1:A1)/2, 0), 1), DATE(2009, ROUND(COLUMNS($A$1:A1)/2, 0), 16)), "MM/DD/YY")&"-"&TEXT(IF(MOD(COLUMNS($A$1:A1), 2), DATE(2009, ROUND(COLUMNS($A$1:A1)/2, 0), 15), DATE(2009, ROUND(COLUMNS($A$1:A1)/2, 0)+1, 1)-1), "MM/DD/YY") copied right as far as necessary.

   I guess the formula can be done a lot smaller.

   See this blog post: http://www.get-digital-help.com/2009/11/04/create-a-custom-date-range-in-excel/

3. clay Says:
   April 22nd, 2011 at 7:18 pm
   
   

   this isn't working when i copy and paste?

4. Oscar Says:
   April 23rd, 2011 at 7:17 am
   
   

   clay,

   There seems to have been a typo.

   You may also need to adjust mm/dd/yy depending on your regional settings.

   http://www.techonthenet.com/excel/formulas/text.php

   Thanks for commenting!

5. Shannon Says:
   June 8th, 2011 at 5:45 pm
   
   

   Hey there! Awesome help!

   I need something similar to this, however, with an added piece.

   I need a formula that if I enter a start date in field B1 such as 6/8/11 it will give me the date ranges for 7 days in fields B3-B14. Does that make sense?

   Basically I want a formula that will tell me when a client is admitted to services on 6/8/11, their week 1 is 6/8/11 to 6/15/11; week 2 is 6/16/11-6/22/11 etc through 12 weeks.

   I want the initial date in B1 to be the only value that I have to change to produce these results. Is that possible?

6. Oscar Says:
   June 9th, 2011 at 8:47 am
   
   

   Shannon,

   read this post: Date ranges for 7 days in excel

   Thank you for commenting!

7. todd Says:
   August 19th, 2011 at 4:37 pm
   
   

   how do i get a range of date to show up.. in the first cell I want 1/11/11 and then each cell to follow the next date
   example
   b3 has 1/11/11 and then i want all 31 days to follow with out having to put each on in b4, b5, b6 and so on?

8. Oscar Says:
   August 22nd, 2011 at 12:32 pm
   
   

   todd,

   Right click and hold on black dot.
   

   Drag down and release right mouse button. Click Fill series.. with left mouse button.

   

9. Michelle Says:
   August 22nd, 2011 at 8:33 pm
   
   

   Hi I need help with a formula. Basically I have a date column and a true of false value column. What I need is a formula that says if the date in Column J is between 4/1/11-3/31/12 then enter 1 in Column M, if not enter 0 in Column M.

   I hope you can help.

   Thanks

10. Oscar Says:
    August 23rd, 2011 at 7:47 am
    
    

    Michelle,

    Formula in cell M1:

    =(J1< =DATE(2012, 3, 12))*(J1>=DATE(2011, 4, 1))
11. Michelle Says:
    August 23rd, 2011 at 4:18 pm
    
    

    perfect thank you Oscar

12. Oscar Says:
    August 24th, 2011 at 1:02 pm
    
    

    Michelle,
    You are welcome!

13. DanL Says:
    September 21st, 2011 at 8:26 pm
    
    

    Thank you!I used my extensive QA background to spot the use of a : and incorrect spacing in the formula for a6....Once again THANK YOU!

    Here's what I ended up with

    =TEXT(DATE(2011, 9, 19)+(COLUMNS($A:A)-1)*7, "mm/dd/yy")&"-"&TEXT(DATE(2011, 9, 19)+(COLUMNS($A:A)-1)*7+6, "mm/dd/yy")

14. Oscar Says:
    September 22nd, 2011 at 9:49 am
    
    

    DanL,

    Thanks! I have updated this post.

15. Richelle Says:
    October 13th, 2011 at 9:53 pm
    
    

    I am trying to do a formula in excel 2003. It is quite complex.

    The start date was June 13 and I need to reflect any blank cells that are 60 days out and any cells that are 91 days out.
    The expirey dates are only for certain cells ($B$3:$F$3 and $N$3:$S$3).
    Hope that makes sense

16. Oscar Says:
    October 17th, 2011 at 8:29 am
    
    

    Richelle,

    can you describe your question in greater detail?

    Also add some example data and what you are trying to achieve?

17. Richelle Says:
    October 17th, 2011 at 7:10 pm
    
    

    Sure,

    Employee Name- Company Start date - WHMIS - Orientation - H/R
    John Smith - 17 Oct 11 - BLANK - 17 Oct 11 -BLANK
    Johanne Smi - 16 Oct 11 - BLANK - BLANK -BLANK

    I have an expirey date of 60 days (will be yellow in color after 60 days from the start date) and 91 days (will be red in color after 91 days from the start date). I only need the expirey dates for the Whimis and HR cells- the orientation cell does not require an expirey date.
    I have tried this:
    =today()-$b$12>60 and =today()-$b$12>91 and =today()-$b$12>59
    that formala does not seem to work...

18. Oscar Says:
    October 19th, 2011 at 8:45 am
    
    

    Richelle,

    You are using absolute cell references in your conditional formatting formula.

    You are also only using cell $b$12 when you say "I only need the expirey dates for the Whimis and HR cells". $b$12 is the company Start date?

19. Richelle Says:
    October 19th, 2011 at 11:10 pm
    
    

    you bet, $b$12 is the company start date. I am wanting the whole line to turn red/yellow when Whimis and HR are over due. The start dates are behind so I dont think (=TODAY)would work.

20. Oscar Says:
    October 21st, 2011 at 10:13 am
    
    

    Richelle,

    I am wanting the whole line to turn red/yellow when Whimis and HR are over due

    In your example table, Whimis and HR are blank?

    Employee Name- Company Start date - WHMIS - Orientation - H/R
    John Smith - 17 Oct 11 - BLANK - 17 Oct 11 -BLANK
    Johanne Smi - 16 Oct 11 - BLANK - BLANK -BLANK

21. Richelle Says:
    October 25th, 2011 at 4:28 pm
    
    

    WHIMIS and H/R are Blank.

22. Oscar Says:
    October 26th, 2011 at 3:10 pm
    
    

    Richelle,

    Download file:Richelle.xls

23. Richelle Says:
    October 27th, 2011 at 6:38 pm
    
    

    Not quite, when the dates are added it does not return to normal.

24. Shirley Says:
    October 28th, 2011 at 11:35 am
    
    

    Dear Oscar

    example: from 20/3/2000 to 31/10/2011 = 11.58, what is the formula i should use to get the result like this?

25. Oscar Says:
    October 28th, 2011 at 12:20 pm
    
    

    Richelle,

    Download file: Richelle1.xls

26. Richelle Says:
    October 28th, 2011 at 5:53 pm
    
    

    You ROCK!
    Thank you so much!

27. Oscar Says:
    October 31st, 2011 at 9:27 am
    
    

    Shirley,

    What are you trying to do?

    Create dates with 11.58 years between them?

    or

    Calculate years between two dates?

28. Shirley Says:
    October 31st, 2011 at 9:33 am
    
    

    Calculate years between two dates.

29. Oscar Says:
    October 31st, 2011 at 10:00 am
    
    

    Shirley,

    Is 0.58 a decimal fraction of a year?

    I get 0.62 in my calculations.

30. Shirley Says:
    October 31st, 2011 at 10:23 am
    
    

    Oscar,

    I think yours is the correct one as i got 0.58 from my system which I found not accurate.

31. Oscar Says:
    November 2nd, 2011 at 2:35 pm
    
    

    Shirley,

    

    Formula in cell c4:

    =TEXT(A4, "DD/MM/yyyy")&" to "&TEXT(B4, "DD/MM/yyyy")&" = "&ROUND((B4-A4)/365, 2)

    This formula does not take leap years into account.

    Excel dates: http://www.cpearson.com/excel/datetime.htm

32. Shirley Says:
    November 3rd, 2011 at 11:35 am
    
    

    thank you Oscar! i got the solution.

33. yanti Says:
    November 22nd, 2011 at 9:32 am
    
    

    Dear Oscar,
    I am really need your help in excel.
    I want to sort some data in date between nov/1/2011 to nov/16/2011.this date is in column K.
    what should I type the formula?

    Thank you.

34. Steve Says:
    December 13th, 2011 at 4:07 pm
    
    

    I have a question as well.
    I have a column consisting of date, user, and another variable (lets call it food) on a sheet:

    so ex.
    A | B | C
    -----------------------
    Date | user2 | carrots

    and it currently counts the number of times a user has eaten a food using the countifs function:

    =COUNTIFS(Sheet2!B2:B9000,"=user1",Sheet2!C2:C9000,"=peas")

    ...this works perfectly.

    The problem Im having is that I want to create another sheet where you can enter a date range and have it display only the results in that range, including the dates.
    Any suggestions/help would be appreciated.

35. Steve Says:
    December 14th, 2011 at 6:04 pm
    
    

    Came up with a solution - make a true/false date range column and pulled the data from that. Thanks anyway, learned a lot!

36. Ian Says:
    December 15th, 2011 at 8:33 am
    
    

    How can I create a data range for different data apart from dates- say personnel ID #s? the formula given here is for dates..

37. Oscar Says:
    December 20th, 2011 at 10:03 pm
    
    

    Ian,

    Can you give me some examples of what you want to achieve?

38. oui Says:
    December 21st, 2011 at 4:59 am
    
    

    Hi Oscar,

    First of all, helpful blog!
    May I ask here regarding finding the correct value based on and within the date range?

    For example, column A contains all the different dates like (not arranged or sorted):
    (1) 1/10/2011
    (2) 23/3/2012
    (3) 20/12/2011
    (4) 3/1/2012

    Reference list:
    In column C, contains the expiration dates with corresponding values in column D.
    For example (chronologically arranged):
    31/05/2010 100,000.00
    31/08/2010 200,000.99
    30/11/2010 300,000.88
    28/02/2011 400,000.77
    31/05/2011 500,000.66
    31/08/2011 600,000.55
    30/11/2011 700,000.44
    29/02/2012 800,000.33
    25/03/2012 900,000.22

    The Result (based on and before the date in Column C but the value in Column D):
    (1) 1/10/2011 400,000.77
    (2) 23/3/2012 800,000.33
    (3) 20/12/2011 700,000.44
    (4) 3/1/2012 800,000.33

    Thanks for your time,
    - oui

39. oui Says:
    December 21st, 2011 at 8:29 pm
    
    

    Hi Oscar,

    Kindly disregard my previous message.

    I think I've got it.
    I've used LOOKUP function.

    Thanks though,
    - oui

40. Matt Says:
    December 23rd, 2011 at 4:45 am
    
    

    I have a weekly report for work I use. My template needs to have the date formatted across one of the upper rows. I need it to read. January 2-6, 2012.

    Thanks,
    Matt

41. Oscar Says:
    January 2nd, 2012 at 12:43 pm
    
    

    Here is a solution to oui´s question:

    

    Learn more: Return value if in range in excel

42. Oscar Says:
    January 2nd, 2012 at 2:35 pm
    
    

    Matt,

    That is not an easy question! I think you have to change formatting for each date.

    Example,

    Value in cell A1: 40910

    1. Select cell A1
    2. Press Ctrl + 1
    3. Go to tab "Number"
    4. Select custom in category
    5. Type MMMM D-"6, " yyyy
    6. Click OK

43. Ingrid Says:
    January 9th, 2012 at 3:21 pm
    
    

    Hi, Oscar! I went through all the Q and As, but not sure my question is answered. It's actually quite simple.

    I need a date range, pulling the date from one column.

    Column I:
    DATE
    17-Jun-11
    Column J:
    Date range (7 days after date in Column I to 21 days after date in Column I).

    Further, is there any way to put into column K whether or not the date is out of the range, with a Y/N, or other means to denote out of range date?

    THANKS for your help, IN advance!-Ingrid

44. Oscar Says:
    January 13th, 2012 at 10:18 am
    
    

    Ingrid,

    Date in cell I2: 17-Jun-11

    Formula in cell J2:

    =TEXT(I2+7,"D-MMM-YY")&" / "&TEXT(I2+21,"D-MMM-YY")
45. Ingrid Says:
    January 13th, 2012 at 5:13 pm
    
    

    This is awesome! THANK YOU!-ij

46. Chaks Says:
    January 16th, 2012 at 6:11 am
    
    

    Hi Oscar, I'm looking for help in Excel.

    In one column, I've dates for few years. I would like to have a column next to it which shows the corresponding Weeks, like Week1, Week2,...till Week5. Can you please help me with this?

47. Oscar Says:
    January 20th, 2012 at 1:51 pm
    
    

    Chaks,

    I hope you´ll find an answer here:

    http://www.rondebruin.nl/weeknumber.htm

    or here:

    http://www.cpearson.com/excel/WeekNumbers.aspx

    When does week1 begin? There are few possible ways to number weeks. See links.

48. Ricardo Says:
    January 25th, 2012 at 8:06 pm
    
    

    Hi Oscar, I need some help for excel.

    I have 1 column containing the 365 days of 2010 written in short date like 01/01/2010. Then I have a second column with some cells containing data and some others left blank.

    I want a formula that will write 1 if at least 50% of cells for a given month are fill and leave a blank cell if not. I want this formula to be aware of the month shift and easy to drag so that I will only get 12 values for a year.

    I think this is impossible but you are the pro.

49. Oscar Says:
    January 30th, 2012 at 3:03 pm
    
    

    Ricardo,

    It is possible!

    Download excel file:
    Ricardo.xls

50. Flyingwater Says:
    February 13th, 2012 at 11:05 pm
    
    

    Hello Oscar,

    I am working with your date range formula from this post (Create a date range using excel formula). I am using the formula from A9, which includes ROW in the formula. Because all of the other cells in those rows are empty I get error warnings. Is there a way to avoid the error warnings regarding empty cells?

    Many thanks.

51. Cyril Says:
    February 14th, 2012 at 5:46 am
    
    

    Chaks
    use this:
    =INT((DAY(cell reference containing your date)-1)/7)+1

52. Cyril Says:
    February 14th, 2012 at 5:54 am
    
    

    Chaks,
    Correction!
    Use this if you want to consider sunday as the first day of the week:
    =INT((13-WEEKDAY(cell reference containing your date)+DAY(cell reference containing your date))/7)
    the first formula would only consider the value of the days, and is not what you were looking for I think...

53. Oscar Says:
    February 20th, 2012 at 2:41 pm
    
    

    Flyingwater,

    I am working with your date range formula from this post (Create a date range using excel formula). I am using the formula from A9, which includes ROW in the formula. Because all of the other cells in those rows are empty I get error warnings. Is there a way to avoid the error warnings regarding empty cells?

    Formula in cell A9:

    =TEXT(DATE(2009, 1, 4)+(ROW(1:1)-1)*7, "mm/dd/yy")&"-"&TEXT(DATE(2009, 1, 4)+(ROW(1:1)-1)*7+6, "mm/dd/yy")

    I don´t think ROW function is the problem here, maybe the "mm/dd/yy" gives you trouble?

54. Adiel Says:
    February 23rd, 2012 at 4:33 pm
    
    

    I am trying a formula which gives me the month between dates what fall between 2 months. For example, 21/02/2012 - 20/03/2012 should return March

55. Oscar Says:
    February 26th, 2012 at 10:54 am
    
    

    Adiel,

    What if the date range is 21/02/2012 - 20/04/2012?

56. Daniel Says:
    February 28th, 2012 at 4:37 pm
    
    

    I am trying to get the date range that was used in a previous query to show up on the front page of my report. The Date range is selected by the user after running the Macro. I want that date range to be listed so when you run the Report you can visually see what date range was pulled. Lets say the Query name was Query1 and the field name was Field2. Oscar, Any idea?

    Thanks!

57. Adiel Says:
    February 29th, 2012 at 5:50 am
    
    

    Hi Oscar
    I am specifically look at 21st of the month to 20th of the next month only. is it is 21/02/2012 - 20/04/2012 it should return an error.

58. Oscar Says:
    February 29th, 2012 at 11:06 am
    
    

    Daniel,

    Hard to say without seeing your workbook.

59. Oscar Says:
    February 29th, 2012 at 11:10 am
    
    

    Adiel,
    Is 21/02/2012 in one cell and 20/03/2012 in a different cell?

60. Glenn Says:
    March 4th, 2012 at 2:07 pm
    
    

    I have a spreadsheet that needs to report the number of days a certain document is over 3 shift days. Where I work is a 9 on and 5 off schedule and I need to exclude any of the days off so that the over 3 is actually only over 3 work days. I am thinking the NETWROKDAYS function may be used for this but can you help with this??

61. Oscar Says:
    March 6th, 2012 at 9:58 am
    
    

    Glenn,

    Can you provide some example data and what you want to achieve?

62. Сергей Says:
    March 15th, 2012 at 10:19 pm
    
    

    Добрый день, Оскар. Помогите решить проблему. Уже который день мучаюсь и все никак не получается посчитать.
    Файл с данными я залил на обменник (mail.ru), вот ссылка, чтобы Вы сразу все увидели и поняли мои замыслы
    http://files.mail.ru/CJAX0S
    Мне необходимо посчитать сумму получившихся цифр за каждый месяц. Хочу задать формулу, которая находит из столбца "А" все даты относящиеся к нужному месяцу и посчитать сумму цифр из столбца "К". Строки в месяце могут добавляться и убираться, следовательно количество цифр изменяется и каждый раз пересчитывать - жутко неудобно.
    Для примера покажите на одной цифре - сумма данных за февраль 2012.
    Результат находится в L23.
    Снизу ТАБ и справа вверху есть разные варианты формул, которые я пытался составить, но так ни одна и не заработала.
    Заранее СПАСИБО! )))

63. Cyril Says:
    March 16th, 2012 at 8:46 am
    
    

    Re Сергей, it could be translated as:
    Hello, Oscar. Help me solve this problem. I am tormented day after day, and still can not get to "count".
    Data file, I filled in the exchanger (mail.ru), here is a link for you to understand all of my designs
    "link to his file"
    I need to calculate the amount of the resulting figures for each month. I want to set a formula that finds the column "A" all dates relating to the desired month and calculate the sum of numbers in column "K". Rows can be added in the month to get out and therefore the number of digits is changed each time to count - terribly "uncomfortable".
    For example, show one figure - the amount of data for February 2012.
    The result is in L23.
    Bottom-TAB and right at the top there are different versions of the formulas I have tried to make, but no one and did not work.
    Thanks in advance! )))

64. Danielle Says:
    March 19th, 2012 at 3:48 pm
    
    

    Hi Oscar,

    I'm hoping you can help me with my formula question. I have a schedule that I am working with and based on one date (ie. 6/4/12) different processes take different times to complete (ie. one step could only take a week, another could take up to 4 weeks). Is there a formula I can use to calculate each step in the process based off of the date range of completion for the first step in the process?

    Let me know if you need more information.

65. Oscar Says:
    March 21st, 2012 at 1:35 pm
    
    

    Danielle,

    Your question seems interesting but I am not sure I fully understand. Can you provide an example?

66. Danielle Says:
    March 21st, 2012 at 5:13 pm
    
    

    Sure! (Sorry i couldn't figure out how to paste this as an excel sheet)

    So for example, if you look at the BBD date at the bottom, all of the steps above it take a certain amount of time to complete and have to be finished on time in order for the project to be complete by 1/4/2013. Instead of typing in manually the date ranges I am trying to write a formula that will allow me to input the project date (ie 1/4/2013) and have all of the other steps populate themselves based on how long they take to complete (ie. the manuscript to CE step could take 2 weeks, the manuscript from CE could take 1 week and so on). I hope that makes sense??

    ie. ONE ROUND From: To:
    Manuscript turnover 6/25/2012 7/30/2012
    Manuscript to CE 8/6/2012
    Manuscript from CE 8/20/2012
    Manuscript to author 8/27/2012
    Manuscript from author 9/10/2012

    Ms to comp 9/3/2012 9/17/2012
    Pages from comp 10/8/2012
    Pages from author 10/22/2012

    Pages to proofreader 10/29/2012
    Pages from proofreader 11/12/2012
    Pages to comp 11/19/2012
    Confirming proofs 11/26/2012
    Ship to printer 12/3/2012

    BBD 1/4/2013

67. Oscar Says:
    March 23rd, 2012 at 9:58 am
    
    

    Danielle,

    read this post:
    Calculate dates in each step in a project based on a finish date

68. Danielle Says:
    March 24th, 2012 at 6:25 pm
    
    

    Thanks! I posted my comment under the other link!

69. Anees Says:
    April 12th, 2012 at 5:21 am
    
    

    Hi,

    The above formula in A9 "Increasing date in a column" works pretty good however I have a small request to change the formula as per my need.

    As I fill the first column in excel with this formula than use + handler on bottom right of the column to drag and copy it to next cell so the date increases. That works but when it reaches the end of month it continues in the same cell. Is it possible so at the end of the month the range would stay within the month instead of increasing to next month?

    Here let me try to explain visually.

    01/01/12-01/07/12
    01/08/12-01/14/12
    01/15/12-01/21/12
    01/22/12-01/28/12
    01/29/12-02/04/12 <-- can this be 01/29/12-01/31/12 ?

    And next month would be per week in each coulmn as well and so on.

    02/01/12-02/04/12
    02/05/12-02/11/12
    02/12/12-02/18/12
    02/19/12-02/25/12
    02/26/12-02/29/12

    03/01/12-03/03/12

    Thank you.

70. Oscar Says:
    April 16th, 2012 at 2:53 pm
    
    

    Anees,

    Your question is really complicated and I like that. But unfortunately I don´t have an answer right now.

71. charles Says:
    April 27th, 2012 at 8:26 pm
    
    

    I have the exact same question as Anees and I was just wondering if you have found a solution. Thanks

72. Oscar Says:
    May 2nd, 2012 at 12:45 pm
    
    

    Anees and charles,

    Read this post:
    Date ranges: Weeks within a month

Leave a Reply