Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Specific Date from a Range
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Jim Jim is offline
external usenet poster
  
Posts: 615
Default 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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
Jim Jim is offline
external usenet poster
  
Posts: 615
Default Forget my last post, here is better question
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

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
Jim Jim is offline
external usenet poster
  
Posts: 615
Default A more though through question
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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 457
Default A more though through question
How about:
=IF(ISNUMBER(A2),A2,DATEVALUE(LEFT(A2,10)))

--
Best Regards,

Luke M
"Jim" wrote in message
...
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



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Create a formula in a date range to locate a specific date - ecel util Excel Discussion (Misc queries) 0 February 19th 07 03:03 PM
find sum if one col = ? within specific date range jrheinschm Excel Discussion (Misc queries) 8 July 5th 06 09:48 PM
Formula for determining if two date columns fall within specific date range Igottabeme Excel Worksheet Functions 2 April 21st 06 02:50 AM
Formula for determining if two date columns fall within specific date range Igottabeme Excel Discussion (Misc queries) 1 April 20th 06 10:03 PM
How do I sum a date specific range of cells? Gabbon Excel Worksheet Functions 3 April 6th 06 08:40 AM


All times are GMT +1. The time now is 11:37 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement