## Create a date range [Formula]

**Question:**I am trying to create an excel spreadsheet that has a date range. Example:

Cell A1 1/4/2009-1/10/2009

Cell B1 1/11/2009-1/17/2009

Cell C1 1/18/2009-1/24/2009

How do I create a formula to do this?

I will in this article discuss what Excel dates actually are, how to use them in formulas and how to create a sequence of dates that you can use as date ranges.

#### What is on this page?

- Explaining Excel dates
- Basic date ranges
- Create a date sequence
- Advanced formula
- Explaining advanced formula
- Download workbook

### What are dates in Excel?

First, what are dates in Excel? They are actually numbers and I will prove it to you, try these steps:

- Type a date in a cell
- Select the cell
- Press CTRL + 1 to open the "Format Cells" dialog box
- Select "General"

- Click OK button
- The cell you selected now has a different formatting. This shows you what dates are in Excel.

Date 11/15/2017 is 43054. Excel starts numbering dates at 1/1/1900 with value 1. Type 1 in a cell and change the cell formatting to "Date" and see what Excel displays.

Date 11/15/2017 is 43053 days from 1/1/1900. This means also that you can't use dates prior to 1/1/1900.

#### Recommended articles

- How to format date values
- Calculate last date in month
- Find latest date based on a condition
- Workdays within date range

### Basic date ranges

You can build a formula or use a built-in feature to build date ranges, read on to learn more.

Now you know that dates in Excel are numbers. You can easily create a date range by adding a number to a date.

The picture below shows a start date 11/15/2017, adding number 7 to that date returns 11/22/2017

This allows you to quickly build date ranges simply by adding a number to a date.

Now select cell B4 and type =C3+1

Copy cell C3 and paste to cell C4.

Relative cell references changes when you copy a cell and paste it to a new cell and I am going to use that now.

Copy cell range B4:C4 and paste it to cells below.

You have now built multiple date ranges using simple mathematics.

#### Create a date sequence

Excel has a great built-in feature that allows you to create number sequences in no time. Since dates are numbers in Excel you can use the same technique to build date ranges.

To build date ranges that have the same range but dates change, follow these steps:

- Type the start date and the end date in a cell each
- Type the second start date an end date in cells below

- Select both date ranges
- Click and hold on black dot

- Drag to cells below

- Release mouse button

Excel has now built date ranges automatically using the two first date ranges as a template.

#### Recommended articles

- Use MEDIAN function to calculate overlapping ranges
- Identify overlapping date ranges
- Count overlapping days across multiple date ranges
- Find missing dates in a set of date ranges
- Category: Overlapping date ranges

### Advanced formula

If you need to build date ranges that have both the start and end date in the same cell you need to build a more complicated formula. The TEXT function lets you format dates out of numbers.

See row 3 in the above picture.

#### Date ranges horizontally

The following formula returns date ranges that exists after the start date, the start date in this case is 1/4/2009, however you can easily change that.

The DATE function creates the number using three arguments. The first argument is the year, the second argument is the month and the third argument is the day.

DATE(*year*, *month*, *day*)

Formula in cell A3:

**2009**,

**1**,

**4**)+(COLUMNS($A:A)-1)*7, "mm/dd/yy")&"-"&TEXT(DATE(

**2009**,

**1**,

**4**)+(COLUMNS($A:A)-1)*7+6, "mm/dd/yy")

Copy cell A3 and paste to cells to the right as far as needed.

This formula returns date ranges that are prior to the given start date.

Formula in A6:

**2009**,

**1**,

**4**)+(2-COLUMN(A:A)-1)*7,"mm/dd/yy")&"-"&TEXT(DATE(

**2009**,

**1**,

**4**)+(2-COLUMN(A:A)-1)*7+6,"mm/dd/yy")

#### Date ranges vertically

Formula in A9:

**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")

Formula in A18:

**2009**,

**1**,

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

**2009**,

**1**,

**4**)+(2-ROW(1:1)-1)*7+6, "mm/dd/yy")

#### How the formula in cell A3 works

The goal with the formula above is to create a date range from Sunday to Saturday. When cell A3 is copied and pasted to the right, the date range adjusts accordingly.

The formula consists of two parts* .* The first part calculates the start date of the date range. I have bolded the start date part of the formula below.

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

#### Step 1 - First part of the formula creates the start date (bolded)

I have bolded the start date in this date range: **01/04/09** - 01/10/09.

TEXT(**DATE(2009, 1, 4)**+(COLUMNS($A:A)-1)*7, "mm/dd/yy")

DATE(2009, 1, 4) is the start date of the date range series.

DATE(2009, 1, 4) returns 39817

(**COLUMN(A:A)**-1)*7 creates a number determined by the current column.

COLUMNS($A:A) returns 1.

COLUMNS($A:A) - 1 returns 0.

(COLUMNS($A:A)-1)*7 returns 0.

This number creates how many days there is between start dates in the date ranges.

Start dates are bolded below.

Example

**01/04/09** - 01/10/09** ; 01/11/09** - 01/17/09 ;** 01/18/09** - 01/10/09

11 - 4 = 7, 18 - 11 = 7.

When the formula is copied and pasted to the right, the relative reference in COLUMNS($A:A) changes.

This cell reference changes as the formula is copied and pasted to the right.

Example,

COLUMNS($A:A) is 1

COLUMNS($A:B) is 2

COLUMNS($A:C) is 3

TEXT(DATE(2009, 1, 4)+0, "mm/dd/yy")

becomes

TEXT(39817 + 0, "mm/dd/yy").

"M/DD/YY" is the specified number format.

TEXT(39817 + 0, "mm/dd/yy") returns 01/04/09

#### Step 2 - Second part of the formula creates the end date

The second part of the formula is

TEXT(DATE(2009, 1, 4)+(COLUMNS($A:A)-1)*7+6, "mm/dd/yy")

The only difference between the first part of the formula:

TEXT(DATE(2009, 1, 4)+(COLUMNS($A:A)-1)*7, "mm/dd/yy")

and the second part of the formula:

TEXT(DATE(2009, 1, 4)+(COLUMNS($A:A)-1)*7**+6**, "mm/dd/yy")

is +6 (bolded)

Example

01/04/09 - 01/10/09

10 - 4 = 6

#### Recommended reading

Plot date ranges in a calendar

The image above demonstrates cells highlighted using a conditional formatting formula based on a table containing date ranges. The calendar […]

The image above demonstrates an array formula in cell E4 that searches for the closest date in column A to the […]

Identify overlapping date ranges

The formula in cell F6 returns TRUE if the date range on the same row overlaps another date range in […]

Find latest date based on a condition

Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download […]

Formula for matching a date within a date range

Table of contents Match a date when a date range is entered in a single cell Match a date when […]

Count dates inside a date range

How do I automatically count dates in a specific date range? Array formula in cell D3: =SUM(IF(($A$2:$A$10<$D$2)*($A$2:$A$10>$D$1), 1, 0)) + […]

How to calculate overlapping time ranges

I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it may be dates, […]

### 150 Responses to “Create a date range [Formula]”

### Leave a Reply to Cyril Clarke

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

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

=TEXT(DATE(2009, 1, 4)+(ROW(1:1)-1)*14, "mm/dd/yy")&"-"&TEXT(DATE(2009, 1, 4)+(ROW(1:1)-1)*14+13, "mm/dd/yy") + ENTER copied down as far as necessary.

Put your START date in BOTH spots that say (2009, 1, 4)...

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: https://www.get-digital-help.com/2009/11/04/create-a-custom-date-range-in-excel/

this isn't working when i copy and paste?

clay,

There seems to have been a typo.

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

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

Thanks for commenting!

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?

Shannon,

read this post: Date ranges for 7 days in excel

Thank you for commenting!

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?

todd,

Right click and hold on black dot.

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

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

Michelle,

Formula in cell M1:

perfect thank you Oscar

Michelle,

You are welcome!

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")

DanL,

Thanks! I have updated this post.

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 :(

Richelle,

can you describe your question in greater detail?

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

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...

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?

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.

Richelle,

I am wanting the whole line to turn red/yellow when Whimis and HR are over dueIn your example table, Whimis and HR are blank?Employee Name- Company Start date - WHMIS - Orientation - H/RJohn Smith - 17 Oct 11 - BLANK - 17 Oct 11 -BLANK

Johanne Smi - 16 Oct 11 - BLANK - BLANK -BLANK

WHIMIS and H/R are Blank.

Richelle,

Download file:Richelle.xls

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

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?

Richelle,

Download file: Richelle1.xls

You ROCK!

Thank you so much!

Shirley,

What are you trying to do?

Create dates with 11.58 years between them?

or

Calculate years between two dates?

Calculate years between two dates. :)

Shirley,

Is 0.58 a decimal fraction of a year?

I get 0.62 in my calculations.

Oscar,

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

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: https://www.cpearson.com/excel/datetime.htm

thank you Oscar! i got the solution.

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.

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.

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

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

Ian,

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

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

Hi Oscar,

Kindly disregard my previous message.

I think I've got it.

I've used LOOKUP function.

Thanks though,

- oui

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

Here is a solution to oui´s question:

Learn more: Return value if in range in excel

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

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

Ingrid,

Date in cell I2: 17-Jun-11

Formula in cell J2:

This is awesome! THANK YOU!-ij

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?

Chaks,

I hope you´ll find an answer here:

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

or here:

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

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

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.

Ricardo,

It is possible!

Download excel file:

Ricardo.xls

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.

Chaks

use this:

=INT((DAY(cell reference containing your date)-1)/7)+1

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...

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:

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

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

Adiel,

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

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!

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.

Daniel,

Hard to say without seeing your workbook.

Adiel,

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

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??

Glenn,

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

Добрый день, Оскар. Помогите решить проблему. Уже который день мучаюсь и все никак не получается посчитать.

Файл с данными я залил на обменник (mail.ru), вот ссылка, чтобы Вы сразу все увидели и поняли мои замыслы

https://files.mail.ru/CJAX0S

Мне необходимо посчитать сумму получившихся цифр за каждый месяц. Хочу задать формулу, которая находит из столбца "А" все даты относящиеся к нужному месяцу и посчитать сумму цифр из столбца "К". Строки в месяце могут добавляться и убираться, следовательно количество цифр изменяется и каждый раз пересчитывать - жутко неудобно.

Для примера покажите на одной цифре - сумма данных за февраль 2012.

Результат находится в L23.

Снизу ТАБ и справа вверху есть разные варианты формул, которые я пытался составить, но так ни одна и не заработала.

Заранее СПАСИБО! )))

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! )))

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.

Danielle,

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

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

Danielle,

read this post:

Calculate dates in each step in a project based on a finish date

Thanks! I posted my comment under the other link!

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.

Anees,

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

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

Anees and charles,

Read this post:

Date ranges: Weeks within a month

hey oscar ... i used the date range formula in your A3 example above. it's great! thanks! now i'm trying to reference these dates ranges in a sumifs statement. for example (if i can do this) ... my current formula is:

=SUMIFS('PO LOG'!$L$5:$L$1036,'PO LOG'!$K$5:$K$1036,SUMMARY!A10,'PO LOG'!$J$5:$J$1036,SUMMARY!D$3)

'PO LOG'!$L$5:$L$1036: column L lists invoice dollars amounts

'PO LOG'!$K$5:$K$1036: column K lists expense codes

SUMMARY!A10: individual expense code

'PO LOG'!$J$5:$J$1036: column J list invoice dates

SUMMARY!D$3: date range created from your A3 formula above (ex: 02/19/12-02/25/12)

this formula is returning zero dollar amounts - which obviously isn't correct. however, i'm not getting an error with the formula. any help you can give would be amazing! thanks so much.

best ... josh

Josh,

Try this formula:

=SUMIFS('PO LOG'!$L$5:$L$1036,'PO LOG'!$K$5:$K$1036,SUMMARY!A10,'PO LOG'!$J$5:$J$1036,"<="&RIGHT(SUMMARY!D$3, LEN(SUMMARY!D$3)-SEARCH("-", SUMMARY!D$3)),'PO LOG'!$J$5:$J$1036,">="&LEFT(SUMMARY!D$3, SEARCH("-", SUMMARY!D$3)-1))

Hi Oscar, im just trying to change the format from 09/02/2012 to 2 Sept is it possible, ive tried format with no luck whatsoever, it just doesnt seem to take any change, even tried to change it to a percentage still no effect. looks like the formula is locked in a format style?

Osman,

Change the formula from "mm/dd/yy" to "dd mmm".

Example formula in cell A3:

=TEXT(DATE(2009, 1, 4)+(COLUMNS($A:A)-1)*7, "dd mmm")&"-"&TEXT(DATE(2009, 1, 4)+(COLUMNS($A:A)-1)*7+6, "dd mmm")

Hi Oscar,

I’m trying to create a date range in an excel calendar. I’ve got the calendar to update to whatever year you type in so that it can be used to check past/future appointments and events, and I’ve got one sheet that shows important events and the dates they occur – when you add to the list it then updates the calendar to show that event.

What I’m having trouble with is showing a week-long event, e.g.: instead of the date being 23/10/2012, the relevant cell on the events sheet would show 23/10/2012-27/10/2012 AND this being updated on the calendar sheet to show the event covering multiple days.

I got a friend to help me with most of the formulas for this, so I’m having trouble working this problem out for myself! I think if I knew how to do the date range to show in a single cell, I might be able to work out how to get it to update the calendar (though if you have any ideas on this as well that would be great!)

Any help would be really appreciated.

Thanks

Green,

Formula in cell E1:

=IF((DATEVALUE(LEFT($A$1, FIND("/", $A$1)-1))<=D1)*(DATEVALUE(RIGHT($A$1, LEN($A$1)-FIND("/", $A$1)))>=D1), $B$1, "")

Hi Oscar

I need your assistance in putting together a formula to show a period of time between two dates. For example:

09/02/1990 to 04/03/2000 to say 10 years 24 days

If the dates have months included then I need t show that as well. We use this to identify periods to credit as experience.

Much appreciated

Cyril

Hi Cyril, why don't you use datedif? such as :

If date start in A1 and date end in B1 then in C1 type:

=DATEDIF(A1,B1,"y")&" years "&DATEDIF(A1,B1,"ym")&" mos "&DATEDIF(A1,B1,"md")&"days"

Would that work for you?

Hi Oscar

Yes this is exactly what I need. Just another piece to add, how do I total several periods with dates formatted like this, I can't just use a SUM formula (I tried). For example:

10 years 2 months 24 days plus

2 years 0 months 16 days

This would be 12 years 3 months and 0 days.

Please advise and your assistance is very much appreciated.

Cyril

Hi Cyril Clarke,

This is cyril not Oscar, for sure Oscar will have a solution that may be far more elegant. As for your query based on my proposed solution, DATEDIF would work with two (2) strings. A date to start and a date to end.

This may not work in your case if the two sets of periods are separated by another period that should not be computed. (hope I make sense).

Or you could use SUMPRODUCT(DATEDIF(RANGE_START,RANGE_END,"y")) ... such as =SUMPRODUCT(DATEDIF(A1:A2,B1:B2,"y"))

Would that work for you?

Hi Cyril

No this formula did not work but it is close as it has returned a zero rather than N/A so there may just be a tweak required.

Also I noticed above that my example shows as:

10 years 2 months 24 days plus

2 years 0 months 16 days

This would be 12 years 3 months and 0 days.

This should actually be 12 years 3 months and 10 days.

Apologies

Cyril

Understood, kindly type:

=DATEDIF(A1,A1+((B2-A2)+(B1-A1)),"y")&" years "&DATEDIF(A1,A1+((B2-A2)+(B1-A1)),"ym")&" month "&DATEDIF(A1,A1+((B2-A2)+(B1-A1)),"md")&" day

that is with data as follows:

2/9/90 5/3/00

2/5/01 2/21/03

In A1 Start First date

In B1 End First date

In A2 Start Second date

in B2 End Second date

this will give you:

12 years 3 month 10 days

would that work for you?

forgot a " after the word day...

Hi Cyril

This is exactly what I needed and is rather eloquent. I have now put in the cells numbers that I need and it is giving me the result I would have expected. I had noticed the missing rabbits ears. It is unusual to be dealing with someone who has the same name as me, it is not a common name.

Thank you very much

Cyril

Glad it worked for you.

Cheers!

Hey guys,

So I have a spreadsheet where each row is a purchase including the Date (year, Month, Day) and cost (along with other variables). I have another table that includes dates that I received paychecks. What I want to do is create a table that sums the spendings in between paychecks.

Thanks!

Figured it out with some crazy codes, this page was extremely helpful! Thanks to all!

How would you modify the formula for just weekdays? So you would have a 5 day range instead of 7 and the next column skips the weekend and starts on the following monday.

Ryan,

Formula in cell A1:

Copy cel A1 and paste down as far as needed.

Hi Oscar

I have an issue which is not related to dates. I need assistance where if a value exists in a cell in my spreadsheet then the cell I am in should return a zero value. I have several values that are conditional on there being a zero in another cell.

We use these variables for moving people from one part of the country to another. For example, a teacher's kids stay in their former location while the teacher finds permanent housing they get one payment whereas if they move with them they get a different payment.

Please advise.

Thanks

Cyril

Re: if a value exists in a cell in my spreadsheet then the cell I am in should return a zero value

Why not using some such as =IF(AB10<>0,0,"EMPTY")

Not knowing the cell reference, just replace AB!) with the cell of reference and "N" with whatever you want in cell is empty.

haaaa, wrap code... formula is =IF(AB10<>0,0,"EMPTY")

less than more than... hopefully Oscar can edit and delete redundant posts...

I am not sure I got less than and more than where you wanted them?

Thanks Oscar.

that is correct. will return a zero if a value (that is equal to zero) exist in the cell, will return "empty", for empty cell and zero values.

Hi Oscar,

I am facing a small problem as follows :

Column A has 12 items with their quantities mentioned. Column B has 14 items with their quantities mentioned.

There are two items in column B that are not in column A and two items in column A not in the other one.

The objective is to merge the two columns without having repetition of any item in the merged column.Once I have this, I want to sum up the quantities of each items from the two columns and show next to the items in the merged column.

Please advise.

Many Thanks

Haroun

Haroun,

1. Put the quantities in separate columns. Perhaps you can do this by using "Text to columns"?

2. Filter unique distinct values from the two columns combined:

https://www.get-digital-help.com/2009/06/16/extract-an-unique-distinct-list-from-two-columns-using-excel-2007-array-formula/

3. Use two Sumif functions to sum up quantities of each item from the two columns.

A pivot table can do all this almost automatically if you combine both lists before creating the pivot table.

https://peltiertech.com/Excel/Pivots/pivottables.htm

Hi Oscar,

Need help with a formula Please. I need to figure out the date range of a cell. So if cell "E2" has a date of 11/23/2012 then that would need to fall under date range of "11/19 - 11/25". I have the following date range I need to identify: "11/12 - 11/18", "11/19 - 11/25", "11/26 - 12/2", "12/3 - 12/9", "12/10 - 12/16", "12/17 - 12/23", "12/24 - 1/1/2013". I tried the formula below but I keep getting a result of "False". I appreciate any help I can get. Thank you

=IF(E2<11/18/12,"11/12 - 11/18",IF(E2<11/25/12,"11/19 - 11/25",IF(E2<12/2/12,"11/26 - 12/2")))

Elizabeth,

read post: Find date range

[...] in Dates, Excel, Search/Lookup on Nov.22, 2012. Email This article to a Friend Elizabeth asks:Hi Oscar, Need help with a formula Please. I need to figure out the date range of a cell. So if cell [...]

Hi Oscar,

I have a question regarding creating a table based on a date range. Say I had a date range (11/27/2012 through 9/27/2033) and I wanted the endpoints to be adjustable. Is there a function I could use, like a PivotTable, to make that happen?

Thanks,

Nate

Nate,

Can you explain in greater detail?

I am trying to count Camper Nights in an RV park, by electric service dates. For Example, a RV camp reserved a pull thru with arrival date of 6/1/12, next column has departure date as 8/15/12. The electric service date (on electric bill) are 6/24/12-7/21/12, 7/22/12-8/23/12. So, I need to count how many camper night fall within each billing cycle. Can you help?

Mr. Oscar,

I have a question.

Cell A1 is haveing date say 01/08/2013

Cell A2 is having no of days say 33 days

Cell A3 is having end date i.e 11/09/2013 by using WORKDAY.INTL(A1,A2,16,$BA$1:$B$10)function.

I need to have the new value in cell a3 every after 33 days(11/09/2013) automatically)

kindly advise

*I need to have the new value in cell a1 every after 33 days(11/09/2013) automatically)

I think you may have covered this at some point, but I can't seem to get the dates to change appearance as I need them to.

What this formula creates is a date that looks like this: 09/23/2013

However, I only want the month and date to be displayed like this 09/23

I need to show 12 weeks of dates so having the whole date takes up too much room.

I have tried changing the displayed value using conditional formatting and the trick you showed Matt in an earlier post, but the look of the dats never changes.

Is this possible?

Heya, your awesome!!! thanks a lot for posting this up. It saved me alooot of time

Cheers,

sandy

How can I have a formula for this:

Column A: Week of 09/30 to 10/04

Column B: Week of 10/07 to 10/11

Column C: Week of 10/14 to 10/18

Column D: Week of 10/20 to 10/24

Thanks for your help

JAH

Hi Oscar,

I am a beginner with excel so my question it might be simply-answered. I m trying to make a graph with the fluctuation of the rates during the year. I have periods in column A and rates in Column B. How can I set date ranges with constant rates? (e.g. from 01/01/2013 to 05/01/2013 $100)?

Thanks,

Dennis

Thanks for your help, I manage to have what I needed but I want the First cell to Automatically change each monday to the new date (Between Monday and Friday). I also Need to have the Phrase "Week of" before the date.

12/09/13-12/13/13 12/16/13-12/20/13 12/23/13-12/27/13 12/30/13-01/03/14

Thanks again

JAH

Hello, I need to return data to a report from a master sheet by date range

Master sheet has several columns, one of which contains timestamps, I need the report sheet to return the data in another column of the row, based on the date range. I can make the timestamps be in the first column if it's easier.

Can you assist please

Hi,

I tried coping and pasting the formula out of Cell A9 to my spreadsheet and got an error msg as follows #VALUE!

Please Help!!!!!!!!!!!!!!!!!!!!!!!!

Matt,

1. Select a #VALUE cell

2. Go to tab "Formulas"

3. Click "Evaluate formula" button

4. Check what part of the formula that is giving you an error.

What is giving you an error?

Hi Oscar sorry for the late reply didn't realise you had responded, the error I am getting is actually #NAME? and the evaluation revealed that the error was in "(DATE(2009, 1, 4)"

It ok i just worked it out i was copying and pasting the "ENTER" into the formula my bad

Now i have another problem, i cant adjust the dates, im trying to enter 15/04/2015 in the format dd/mm/yyyy but it just wont commit.

Hi, sir i'd like you to help me about the formula to show the date after a period day. Ex. i have a meeting on 22/04/2015 and i'll have a meeting again after 21 days so i want to know the date will be on.

thank

Puthea

Enter this formula: =DATE(2015,4,22)+21

thank you Oscar i try it now

HI Oscar

Thank you for your help.

I am looking to create a formula that highlights a cell in red if the invoice date + 91 days is after today.

=if(TODAY>L114+91)then RED

It must also keep the L114+91 (example) Date in the cell

I hope thats clear

James

James

Try this conditional formatting formula:

=L114>TODAY()+91

Hi Oscar sorry for the late reply didn't realise you had responded, the error I am getting is actually #NAME? and the evaluation revealed that the error was in "(DATE(2009, 1, 4)"

Hi, I want to only mention Start & End date in Cell C9 & C10 respectively. Based on this workdays calculated I want to display all dates between Start/ End date in a row.

For Example: Start Date = 19 May 2015, End Date = 23 May 2015, WorkDays = 4

Then in row 4, I want to display 20 May, 21 May, 22 May. 23 May starting form F4 cell. I also want to exclude Saturday and Sunday if they are falling between Start & End Date.

Hi, I want to only mention Start & End date in Cell C9 & C10 respectively. Based on this workdays calculated I want to display all dates between Start/ End date in a row.

For Example: Start Date = 19 May 2015, End Date = 23 May 2015, WorkDays = 4

Then in row 4, I want to display 20 May, 21 May, 22 May. 23 May starting form F4 cell. I also want to exclude Saturday and Sunday if they are falling between Start & End Date.

Hi oscar.. Can you give me a formula to calculate a starting date to present and the result would be in how many years months and day. Example start on january 1, 2015 and today is feb 2,2015 the result would be 0-year, 1-month, 2-days

Sherwin,

Take a look at this:

https://excelsemipro.com/2011/01/how-many-years-months-and-days-has-it-been/

Hi Oscar, I need the First cell to Automatically change each monday to the new date (Between Monday and Friday). I also Need to have the Phrase "Week of" before the date.

12/09/13-12/13/13 12/16/13-12/20/13 12/23/13-12/27/13 12/30/13-01/03/14

How can I do this?.

Thanks

JAH

Hi can you create a formula where if the payment end dates fall within the following date range:

from the 1st through the 7th day of the month;

from the 8th through the 14th day of the month;

from the 15th through the 21st day of the month;

from the 22nd through the last day of the month.

that the CRA remittance is due on the 3rd working day excluding Sat, Sun and stats below:

1-Jan-2016

8-Feb-2016

25-Mar-2016

28-Mar-2016

23-May-2016

1-Jul-2016

1-Aug-2016

5-Sep-2016

10-Oct-2016

11-Nov-2016

26-Dec-2016

27-Dec-2016

Hi..............

Sir, my question is???

My Product Name - Samsung-E1200 just time price - 1120/-

and then price drop it this product - Samsung-E1200 - 1175/-

so sir howes condition apply i m not understand????

so sir please suggest me............. and question is ????

01-feb to 14-feb price 1120/-

then 15-feb to 29-feb- price change 1175/-

so suggest me???????

I want a help from you in excel

hi Oscar,

I am stuck on an excel problem.

I have thousands of cells of data.

In one column I have manually entered dates. Sometimes the date is a single day (1/1/2003). Other times it is a manually entered date range (1/1/2003-5/1/2003). A few columns later each entry has a numerical value to capture the scale of something I am measuring. The numbers are like: 10, 126, 480; and so on.

Elsewhere in the spread sheet I want to return a single value for the sum of all the numbers that correspond to a specific date, So for example,

TAB 1

DATE VALUE

1/1/2003 20

1/1/2003-5/1/2003 10 (i.e. 10 per day)

1/1/2003-20/6/2007 10 (i.e. 10 per day)

and so on....

TAB 2

DATE VALUE (I need the formula to calculate below)

1/1/2003 40

2/1/2003 20

3/1/2003 20

4/1/2003 20

5/1/2003 20

6/1/2003 10

Is that clear?

Can you help me with that?

ben

ben,

Array formula in cell B8:

=SUM(IF((A8>=$A$2:$A$5)*(A8<=$B$2:$B$5),$C$2:$C$5,""))

Hi Oscar,

Nice formula, I used the Row 3 format - =TEXT(IF(MOD(XXX formula, but need your assistance for getting this working on my computer; when I copy paste the formula it, works for the first cell only,as the gap I am trying to achieve is 7 days, but when I drag the formula to adjacent column, the gap incrases to 16 days. Can you suggest a fix for this please?

I tweaked the formula as below, but needs refinement from an expert like yourself

=TEXT(IF(MOD(COLUMNS($I$5:I5),2), DATE(2016,ROUND(COLUMNS($I$5:I5)/2,0), 1),DATE(2016,ROUND(COLUMNS($I$5:I5)/2, 0),7)),"DD/MM/YY")&" - "&TEXT(IF(MOD(COLUMNS($I$5:I5),2), DATE(2016,ROUND(COLUMNS($I$5:I5)/2,0), 7),DATE(2016,ROUND(COLUMNS($I$5:I5)/2, 0)+1,1)-1),"DD/MM/YY")

Trying to achieve 01/01/16-7/01/16, 8/01/16-15/01/16, using dd/mm/yy format.

Thanks,

Nav

Nav

Is this what you are looking for?

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

Hi Oscar! How are you? First of all I'd like to tell you how admire your work and the dedication you put in it till these days.

I've tried to duplicate the A3 formula in GOOGLE SPREADSHEET - but no success.

Could you help me?

Thank's already pal

Hi Oscar,

I'm trying to make a weekly candlestick chart for my retirement fund's daily share prices. I can make such a chart but the values have to be arranged as ...

A B C D E

Date Open High Low Close

My problem is that the info is in a csv file and when I open it the data is arranged as ...

A B

Date Price

So is there a way to programmatically rearrange the later to the former?

Thanks in advance,

Eric

Eric

To my knowledge, you cant extract the open, high, or low out of a closing price. You source data needs to be in greater detail.

Hi,

I'm trying to get the formula to generate this:

June 24 - July 6

July 7 - July 20

July 21 - August 3

August 4 - August 17

August 18 – August 31

September 1 - September 14

September 15- September 28

September 29 - October 12

October 13 – October 26

October 27 - November 9

November 10 - November 23

November 24 - December 7

December 8 - December 21

December 22 - January 4

January 5 - January 18

January 19 - February 1

February 2 - February 15

February 16 - March 1

March 2 - March 15

March 16– March 29

March 30 - April 12

April 13 – April 26

April 27 - May 10

May 11 - May 24

May 25 - June 7

June 8 - June 23

Note that all ranges are 14 days, but the first and last date ranges are not 14 days.

Please help. I can figure out the end date

Spence, is there some logic in that sequence? I know 14 days but that doesn't help much.

Do you want a formula so you simply can provide a start date and the formula does the rest or how will it work?

Oscar,

I would like to add an empty row between each date range but excel seems to lose the formula when I do. It will start to skip weeks in the sequence. Can you help?

Devon,

This regular formula seems to work:

=IF(MOD(ROWS($A$1:A1), 2), TEXT(DATE(2009, 1, 4)+(INT(ROW(2:2)/2)-1)*7, "mm/dd/yy")&"-"&TEXT(DATE(2009, 1, 4)+(INT(ROW(2:2)/2)-1)*7+6, "mm/dd/yy"), "")

[…] got this from Carol Weideman, thank you! I think it is really simple and […]

hi

How to set date range like

1 Jun 18 in first cloume

formula for calculate 7+4 days

and how to set error If date less then 7 days and more then 11 days

Hi

I have this formula (=SUMIFS($C$14:$C$1000,$A$14:$A$1000,">="&DATE($A$1,8,1),$A$14:$A$1000,"<"&DATE($A$1,9,1),$F$14:$F$1000,$AA1)) which works but I want it to show from the 4th Sunday of a month to the 4th Sunday of the next month.

Thank You

Alan

Hi Alan,

I have added your question and an answer to this article:

https://www.get-digital-help.com/2018/01/08/excels-sumifs-function-explained/

`Function daterange(targetbegin As Range, targetend As Range, Optional VolatileParameter As Variant) As Variant`

Dim begindate As Date

Dim enddate As Date

begindate = targetbegin.Value

enddate = targetend.Value

daterange = begindate & " - " & enddate

`End Function`

in the formula bar: =daterange(startcell, endcell, NOW())

Hi,

How can I get the date change automatically from another cell in the same spreadsheet? For instance, I typed in today's date, 23 Jun in Cell A2 and I want the dates changed in Cell A38, A74, A110 and A146 just by changing the date in Cell A2.

Cheerio,

Ida