Specific Date from a Range

Hello,

I have a spreadsheet that has a column of dates that look like this:

Activity Dates
2/15/2010
03/01/2010-03/31/2010
03/01/2010-03/31/2010
03/01/2010-03/31/2010
03/01/2010-03/31/2010
03/01/2010-03/31/2010
03/01/2010-03/31/2010
03/01/2010-03/31/2010
03/01/2010-03/31/2010
03/01/2010-03/31/2010
2/28/2010
2/28/2010
2/28/2010
2/28/2010
2/28/2010
03/01/2010-03/31/2010
03/01/2010-03/31/2010
03/01/2010-03/31/2010
2/1/2010
03/01/2010-03/31/2010
03/01/2010-03/31/2010
03/01/2010-03/31/2010
03/01/2010-03/31/2010
03/01/2010-03/31/2010
2/25/2010
03/01/2010-03/31/2010

I'm currently using this formula to pull the data into another sheet 
(=IF(Input_Sheet!Q5=""," ",Input_Sheet!Q5)).  

My question: Is there a formula I canuse that will keep the specifi dates in 
this column ( for example 2/25/2010) yet calculate a specific date from the 
range cell. 
While calculating a mid range for the date that appear in a range, turning 
03/01/2010-03/31/2010 into 03/15/2010.  As well as make the formula flexable 
enough to read each month of the year (04/01/2010-04/31/2010) into April 15, 
2010, etc...

I hopr this is clear and thanks for the help.
Jim
 
0
Utf
3/29/2010 1:08:01 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
840 Views

Similar Articles

[PageSpeed] 15

What formula do I use to show only 03/01/2010 from data that looks like this: 
03/01/2010-03/31/2010


"Jim" wrote:

> Hello,
> 
> I have a spreadsheet that has a column of dates that look like this:
> 
> Activity Dates
> 2/15/2010
> 03/01/2010-03/31/2010
> 03/01/2010-03/31/2010
> 03/01/2010-03/31/2010
> 03/01/2010-03/31/2010
> 03/01/2010-03/31/2010
> 03/01/2010-03/31/2010
> 03/01/2010-03/31/2010
> 03/01/2010-03/31/2010
> 03/01/2010-03/31/2010
> 2/28/2010
> 2/28/2010
> 2/28/2010
> 2/28/2010
> 2/28/2010
> 03/01/2010-03/31/2010
> 03/01/2010-03/31/2010
> 03/01/2010-03/31/2010
> 2/1/2010
> 03/01/2010-03/31/2010
> 03/01/2010-03/31/2010
> 03/01/2010-03/31/2010
> 03/01/2010-03/31/2010
> 03/01/2010-03/31/2010
> 2/25/2010
> 03/01/2010-03/31/2010
> 
> I'm currently using this formula to pull the data into another sheet 
> (=IF(Input_Sheet!Q5=""," ",Input_Sheet!Q5)).  
> 
> My question: Is there a formula I canuse that will keep the specifi dates in 
> this column ( for example 2/25/2010) yet calculate a specific date from the 
> range cell. 
> While calculating a mid range for the date that appear in a range, turning 
> 03/01/2010-03/31/2010 into 03/15/2010.  As well as make the formula flexable 
> enough to read each month of the year (04/01/2010-04/31/2010) into April 15, 
> 2010, etc...
> 
> I hopr this is clear and thanks for the help.
> Jim
>  
0
Utf
3/29/2010 1:14:01 PM
What formula do I use to trim a date from a date range when some of hte cells 
have a date I do nto want to trim?

See my first post for an example.

Thank you

"Jim" wrote:

> What formula do I use to show only 03/01/2010 from data that looks like this: 
> 03/01/2010-03/31/2010
> 
> 
> "Jim" wrote:
> 
> > Hello,
> > 
> > I have a spreadsheet that has a column of dates that look like this:
> > 
> > Activity Dates
> > 2/15/2010
> > 03/01/2010-03/31/2010
> > 03/01/2010-03/31/2010
> > 03/01/2010-03/31/2010
> > 03/01/2010-03/31/2010
> > 03/01/2010-03/31/2010
> > 03/01/2010-03/31/2010
> > 03/01/2010-03/31/2010
> > 03/01/2010-03/31/2010
> > 03/01/2010-03/31/2010
> > 2/28/2010
> > 2/28/2010
> > 2/28/2010
> > 2/28/2010
> > 2/28/2010
> > 03/01/2010-03/31/2010
> > 03/01/2010-03/31/2010
> > 03/01/2010-03/31/2010
> > 2/1/2010
> > 03/01/2010-03/31/2010
> > 03/01/2010-03/31/2010
> > 03/01/2010-03/31/2010
> > 03/01/2010-03/31/2010
> > 03/01/2010-03/31/2010
> > 2/25/2010
> > 03/01/2010-03/31/2010
> > 
> > I'm currently using this formula to pull the data into another sheet 
> > (=IF(Input_Sheet!Q5=""," ",Input_Sheet!Q5)).  
> > 
> > My question: Is there a formula I canuse that will keep the specifi dates in 
> > this column ( for example 2/25/2010) yet calculate a specific date from the 
> > range cell. 
> > While calculating a mid range for the date that appear in a range, turning 
> > 03/01/2010-03/31/2010 into 03/15/2010.  As well as make the formula flexable 
> > enough to read each month of the year (04/01/2010-04/31/2010) into April 15, 
> > 2010, etc...
> > 
> > I hopr this is clear and thanks for the help.
> > Jim
> >  
0
Utf
3/29/2010 1:18:01 PM
How about:
=IF(ISNUMBER(A2),A2,DATEVALUE(LEFT(A2,10)))

-- 
Best Regards,

Luke M
"Jim" <Jim@discussions.microsoft.com> wrote in message 
news:5DC4082D-311A-47E4-9FE4-E905928E8BAF@microsoft.com...
> What formula do I use to trim a date from a date range when some of hte 
> cells
> have a date I do nto want to trim?
>
> See my first post for an example.
>
> Thank you
>
> "Jim" wrote:
>
>> What formula do I use to show only 03/01/2010 from data that looks like 
>> this:
>> 03/01/2010-03/31/2010
>>
>>
>> "Jim" wrote:
>>
>> > Hello,
>> >
>> > I have a spreadsheet that has a column of dates that look like this:
>> >
>> > Activity Dates
>> > 2/15/2010
>> > 03/01/2010-03/31/2010
>> > 03/01/2010-03/31/2010
>> > 03/01/2010-03/31/2010
>> > 03/01/2010-03/31/2010
>> > 03/01/2010-03/31/2010
>> > 03/01/2010-03/31/2010
>> > 03/01/2010-03/31/2010
>> > 03/01/2010-03/31/2010
>> > 03/01/2010-03/31/2010
>> > 2/28/2010
>> > 2/28/2010
>> > 2/28/2010
>> > 2/28/2010
>> > 2/28/2010
>> > 03/01/2010-03/31/2010
>> > 03/01/2010-03/31/2010
>> > 03/01/2010-03/31/2010
>> > 2/1/2010
>> > 03/01/2010-03/31/2010
>> > 03/01/2010-03/31/2010
>> > 03/01/2010-03/31/2010
>> > 03/01/2010-03/31/2010
>> > 03/01/2010-03/31/2010
>> > 2/25/2010
>> > 03/01/2010-03/31/2010
>> >
>> > I'm currently using this formula to pull the data into another sheet
>> > (=IF(Input_Sheet!Q5=""," ",Input_Sheet!Q5)).
>> >
>> > My question: Is there a formula I canuse that will keep the specifi 
>> > dates in
>> > this column ( for example 2/25/2010) yet calculate a specific date from 
>> > the
>> > range cell.
>> > While calculating a mid range for the date that appear in a range, 
>> > turning
>> > 03/01/2010-03/31/2010 into 03/15/2010.  As well as make the formula 
>> > flexable
>> > enough to read each month of the year (04/01/2010-04/31/2010) into 
>> > April 15,
>> > 2010, etc...
>> >
>> > I hopr this is clear and thanks for the help.
>> > Jim
>> > 


0
Luke
3/29/2010 1:34:30 PM
Reply:

Similar Artilces:

picking from a specific list in a combo box
I have a form that has a combo box called "contact" that refers to a table called "Contacts". I would like to pick from a list of contacts for a specific client without displaying all of the names in the contacts table, just the ones that match the name of the client on the main form. I've tried joining the ID's in the relationships but it doesn't seem to work for me. Any help appreciated! Thanks Lee -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200802/1 Try to set the controlsource of the combobox to a qu...

Selecting data between to specific dates in a form
Sorry if this is an easy one but I am new to this area. I have created a query from our SQL database that list all hours booked by eack employee and the date booked, from when we 1st started collectin thi information. I am wanting to create a form that anyone can go into and select a date range so that the can calculate the hours between these dates. Th other problem that I have got is that the date are in number format and not in date. How dan I format this correctly? Regards The first thing to do will be to convert your number field into a real date/time field. To do that: 1. Ope...

Date Recurrence Pattern
I need to replicate Outlook Task functionality in a table containing a list of tasks. When the user checks the record as done, I want to generate a new record with the next due date based on the stored recurrence pattern for that taks. Before I spend my wheels for hours/days, has anyone dealt with this kind of thing before? As you probably guessed, Mitch, It's not a simple task. There are issues such as open-ended recurring appointments (theoretically into the future infinitely), and removing/rescheduling particular appointments in the series. At the simplest level, your tab...

E-Mailing a specific page of a report
I have lengthy report that is grouped by Zone ID. I want to e-mail the zone supervisor the pages of this report as an attachment (in a snapshot format) that deal with just their zone. currently we e-mail the whole report and the hunt for their information. The zone supervisor e-mail address is in a table called 'tblZones' I don't want to have to create a report with a corresponding query (if possible) for each zone. I am currently using sendobject to deliver this report. However we are not always notified of supervisor changes and would like to use the e-mail address...

Sum products prices that are between two dates
Hi all, I was wondering if someone could tell me what the formula would be if I wanted to add the prices of products that were purchased between two dates. If column A was formatted with the dates between 01/01/06 and 31/01/06 and column B with the prices. Thanks in advance Maddoktor Hi Maddoktor In cell D1, enter the Start date required in cell E1 enter the end date required. In F1 =SUMPRODUCT(--($A$1:$A$100>=D1),--($A$1:$A$100<=E1),$B$1:$B$100) Change the ranges to suit. -- Regards Roger Govier "Maddoktor" <maddoktor@clinic.com> wrote in message news:e1v...

Date functions for dates pre-1900
I am trying to use a function that will tell me the day of the week for a date such as 6/14/1851. It works fine when I enter a date from 1900 or later but not for 1851 or something else in the 19C. Is there a way of doing it in Excel??? Thanks See http://j-walk.com/ss/excel/files/xdate.htm -- Kind regards, Niek Otten Microsoft MVP - Excel "Stephen Larivee" <NOlariveeslSPAM@yahoo.PLEASEcom> wrote in message news:sJudnaknbsbfFUPfRVn-sA@comcast.com... >I am trying to use a function that will tell me the day of the week for a >date such as 6/14/1851. It wo...

Outlook 2K Date Navigator
Hi All, I have a user that cannot get the Date Navigator to display in the Outlook 2K calendar. No matter what view he chooses, the only thing that is displayed when he views his calendar is Task Pad. Any suggestions? Thanks & Ciao Hi Tony Open Outlook with the /cleanviews switch to see if that helps. http://www.howto-outlook.com/howto/commandlineswitches.htm Let me know if that helps or not. -- Kathleen Orland Outlook Tips: http://www.outlook-tips.net/ http://www.howto-outlook.com/ "Tony" wrote: > Hi All, > > I have a user that cannot get the Date...

Go Offline Specifications
Hi there! I am trying to find a document referring to different scenarios of working offline and their specifications (bytes transfered, estimated time of synchronization, connection types etc). Can you help me? Propose a Microsoft or external source for this? Thanks in advance. Elena I have not seen any document discussing this. Most offline docs talk about what is needed to do it rather than drill into details. Microsoft always recommends you do your initial Go Offline in outlook while connected to the comapny network. We have found that as long as your remote location has a fast conn...

how to enter date and time
I like to enter date and time in a cell, can someone tell me the best way to enter? Enter "Ctrl+;" "Soe" <soe@soe> wrote in message news:uM5WWMNKEHA.3628@TK2MSFTNGP12.phx.gbl... > I like to enter date and time in a cell, can someone tell me the best way to > enter? > > Hi Soe Lots of info on Dates and Times here http://www.ozgrid.com/Excel/ExcelDateandTimes.htm For the current date use; Ctrl+; For the curent time use; Ctrl+Shift+: For the current date and time, use; Ctrl+; Space Ctrl+Shift+: ** Posted via: http://www.ozgrid.com Excel Template...

Posted payment with wrong document date
Hi, 2 years ago a payment was posted to a customer account with the wrong document date (a future document date year 2204 to be exact). The posting date was ok. The Customer Inquery screen shows 0 amount remaining but the aging report shows the invoice unpaid. What would be the best course of action to fix this? Thanks in advance. ...

Using a filtered list as data range for a chart
Hello, I would like to chart a cumulative list of numbers I have in one column (B) on the Y axis and increment the X axis for each Y number i.e. X runs 1,2,3 etc. By cumulative, I mean the column B rows are derived from column A eg. B1=A1, B2=A1+A2, B3=A2+A3 etc. The problem I'm having is how to be able to filter the list of numbers in column B (eg. a subset of numbers or rows for month) for the Y axis and have the X axis run 1-31 to show a monthly chart. Ideally, I would like a drop down list to select the month and have the chart change after a new month is selected. Thanks in adv...

sending mails to specific mail addresses rather than specific doma
I want to set up exchange in my organization.But in my organization security is very important.I want to restrict users from sending mails only to specific mail address. for example john@yahoo.com but john should not be able to send mails to any one else on the yahoo like sam@yahoo.com. I studied a lot about smtp connectors. but not able to find a solution for this in exchange 2003. Is it possible by any means. Can anyone help me abt this even if microsoft also.Otherwise I am not able to set up exchange server in my organization. Thanks in advance On Tue, 16 Jan 2007 18:43:01 -0800, mang...

Save as Current Date / Time
OK I'm learning to USE the Record Macro feature on a spreadsheet and I need the sheet to do two things: I need to be able to have a macro SAVE the file as the current date/time using a set formating. At the moment I have a cell set up for the NOW() command that gets updated at the beginning of the Macro. When I go to "Save As" the macro record feature can't seem to compute that the value in the File Name block is the value from another cell displaying only the result of the NOW() command. I'll neet to make a button for it when I'm done. But I know there&#...

Tally the figures acquired from the same date
Hi, I am working on a spreadsheet with electricity meter reading throughout a year. The reading were taken multiple times on one day. I tried everything i know to works out the monthly average of the electricity usage but i failed. I managed to flag the day and month on each figure and thats as far as I go so far. Can anyone think of a formula to do the task. An extract of the spreadsheet: day Mth Yr Time V_Ave_PC6 9 9 2008 18:00:00 244.3 9 9 2008 18:30:00 243.4 9 9 2008 19:00:00 243.7 9 9 2008 19:30:00 241.9 9 9 2008 20:00:00 242.6 10 9 2008 20:30:00 242.4 10 9 2008 21:00:00...

I am looking for a date add-in using a calendar
I am doing a spreadsheet with dates and instead of individually typing in each and every one, I was wondering if there was an add-in that when you click on the cell, a calendar pops up and you choose the date. Kind of like when you're in Access and you click on the date and the calendar appears and you can choose what ever date you want. This is a fantastic idea for a VBA project. You should re-post in the Programming group. -- Gary's Student "Elf_chick" wrote: > I am doing a spreadsheet with dates and instead of individually typing in > each and every one, ...

Filter in between dates
Hi Everyone, Using Excel 2003. I have a list of patients who were on a particular therapy and in a certain unit. Many where on the therapy in other units before getting transferred or on the therapy after they left. I need to find the patients who started on a therapy after they arrived in a unit and who were off the therapy before they left the unit. My Date units show me Date and Time in the same field. They are named.. PtLocStDtTm PtLocEndDtTm PtThpyStDtTm PtThpyEndDtTm Thanks, Linda If desired, send your file to my address below. I will only look if: 1. You se...

plz help !! Undefined function 'Date' in expression
hi all please help i got mad !!!!!! i was using date() function to get the current in access query & vba and both doesn't work and i got this msg Undefined function 'Date' in expression i tried all the tricks in the internet about fix the ref. and no way .still as it is any new tips plz or another way to get the current date thanks all bye -- Message posted via http://www.accessmonster.com The most likely problem is that your database has a problem with library references. To fix it, see: Solving Problems with Library References at: http://all...

Set specific template in Outlook
Hi, Is there any way via C# or vbs i can set witch template outlook uses when the user starts his Outlook, and just general settings, its a pain in the *** to do it manually on 100+ machines.... Or how do you do this, I would love to hear other ways to manage this... kind regards Maybe, if you define wht you mean by "template Outlook uses when the user starts his Outlook." -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx "news.microsof...

time & date settings
The time and dateeetings keep reverting to 2007, and incorrect time. I reset it, and it stays that way until I shutdown. On reboot the time and date revert to the incorrect values. Any suggestions as to why this happens? Is there a way to keep the clock and/or date correct? Thanks in advance for any suggestions. Your computer's CMOS battery has run out of power and needs to be replaced. It means taking the computer apart, locating the battery (usually a CR2032 lithium battery) and replace.. -- Thomas Wendell Helsinki, Finland Translations to/from FI not always accu...

Condition Formatting based on due date
I have a spreadsheet where I am trying to color a row based on how close I am to a due date. The basic logic is: I need to color a row yellow if today's date is less than 45 days from the due date in column B or color the same row red if today's date is less than 14 days from the due date in column B. I am stumped, any suggestions would be greatly appreciated. Thanks, jaggy Select the entire sheet (A1 active), then apply the CF as Cond1: =AND($B1<>"",$B1-TODAY()<14) Format: Red fill Cond2: =AND($B1<>"",$B1-TODAY()>=14,$B1-TODAY()<45) Form...

CRM Advanced Find on a date "older than X weeks"
User is trying to create a view to find cases "Older than X number of weeks". Currently they have to use the "On or Before" and specify the Date each time which is not dynamic. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.c...

import specifications
In ACCESS office 2003, i could change the import specs lusing the advanced button. i can't figure out how to edit an existing speps in ACCESS 2007 without creating a new spec. The only thing I can change is the name and the path. ...

getting dates on my graph
I can not solve the problem with being able to put the dates in my graph. Since it is a bar chart the x and y axis are flipped. How can I get the dates put in on the y- axis. I would be happy to be able to send the file if someone thinks they can solve this. thanks jj ...

Opportunity / View / Close Date
I would like to be able to create a View showing All Opportunities that will close in the Next 31 Days / Next 365 Days / etc. I can only find the Next 7 Days. Any suggestions how to solve this puzzle without working with filtered Reports? Thanks, Richard Newtel Essence The current v1.2 "Advance Find" and "Filter View" will not support what you are asking for. You will need to looking into SDK or Report customization. Good luck. Frank Lee Workopia, Inc. http://www.workopia.com/Links.htm San Francisco, CA "Richard Fransen" wrote: > I would like to be...

Code to hide window elements in a specific file
Hi there, I have the following code which works to remove toolbars when a specific file is opened. But I would also like for the formula bar, status bar and sheet tabs to be hidden when the file is opened. These must, however, all be visible again when any other file is opened. Please help! Code in This Workbook: Private Sub Workbook_Activate() Dim bar As CommandBar For Each bar In Application.CommandBars bar.Enabled = False Next End Sub Private Sub Workbook_Deactivate() Dim bar As CommandBar For Each bar In Application.CommandBars bar.Enabled = True Next...