Create a date range using excel formula
Question: I am trying to create an excel spreadsheet that has a date range.
Example: Cell A1 1/4/09-1/10/09
Cell B1 1/11/09-1/17/09
Cell C1 1/18/09-1/24/09
How do I create a formula to do this?
Answer: See row 3 and formula below picture.
Formula in A3:
Formula in A6:
Formula in A9:
Formula in A18:
How to use formulas in your workbook
Copy the formula to your sheet. To change start date, change bolded values in the above formula.
How the formula in cell A3 works
The goal is to create a date range from sunday to saturday.
When cell A3 is copied and pasted to the right, the date range adjusts.
The formula consists of two parts. Start date and end 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 start date
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
Functions used in this blog post:
DATE(year,month,day) returns the number that represents the datein Microsoft Office Excel date-time code
TEXT(value, format_text)
Converts a value to text in a specific number format
ROW(reference) returns the rownumber of a reference
Related articles:
- Create a monthly date range in excel
- Create a quartely date range in excel
- Create a custom date range in excel
Related posts:
Create a monthly date range in excel
Create a quartely date range in excel
Create a custom date range in excel
Recommended categories
how can i get the date range as : 01/01/09 - 01/15/09, 01/16/09 - 01/31/09 and so on
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/
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.
http://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 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
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: http://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:
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.
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), вот ссылка, чтобы Вы сразу все увидели и поняли мои замыслы
http://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:
http://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.
http://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?