Return Date using two dates and a word
 

I am trying to make a time table based of a completion date A1 for specific
tasks.

Some completion dates have a range of dates. Here is what i need to do. If
cell A1 is 12/1/07 i want a cell to return "9-1-07 to 10-1-07". I have no
problem getting cells to return a simple 9-1-07 or 10-1-07 but when i try to
use concatenate or $ Excel only displays the date value ie 39249 for 6/16/07.
So instead of getting 9/1/07 to 10/1/07 to display in cell i would get
39429 to 39459 or something to that effect. Any suggestions.

Also i am currently setting up formulas to display say 45 days before
completion date at =date(year(A1), month(A1)-1, day(A1)-15. However, this
doesn't seem totally accurate. Any suggestions.

Return Date using two dates and a word
 

=TEXT(A1-3,"dd/mm/yy")&" - "&TEXT(A1-1,"dd/mm/yy")

second part

=DATE(YEAR(A1),MONTH(A1),DAY(A1)-45)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"pblenis" wrote in message
...
I am trying to make a time table based of a completion date A1 for specific
tasks.

Some completion dates have a range of dates. Here is what i need to do.
If
cell A1 is 12/1/07 i want a cell to return "9-1-07 to 10-1-07". I have no
problem getting cells to return a simple 9-1-07 or 10-1-07 but when i try
to
use concatenate or $ Excel only displays the date value ie 39249 for
6/16/07.
So instead of getting 9/1/07 to 10/1/07 to display in cell i would get
39429 to 39459 or something to that effect. Any suggestions.

Also i am currently setting up formulas to display say 45 days before
completion date at =date(year(A1), month(A1)-1, day(A1)-15. However, this
doesn't seem totally accurate. Any suggestions.

Return Date using two dates and a word
 

Thanks Bob that did what i was looking for. Now i do have another issue i
just ran into. I need to make sure the values returned for completion dates
always fall on a 1st or 15th (never mind if they are weekends its for generic
reference). Is there a rounding function for months and days?
"Bob Phillips" wrote:

=TEXT(A1-3,"dd/mm/yy")&" - "&TEXT(A1-1,"dd/mm/yy")

second part

=DATE(YEAR(A1),MONTH(A1),DAY(A1)-45)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"pblenis" wrote in message
...
I am trying to make a time table based of a completion date A1 for specific
tasks.

Some completion dates have a range of dates. Here is what i need to do.
If
cell A1 is 12/1/07 i want a cell to return "9-1-07 to 10-1-07". I have no
problem getting cells to return a simple 9-1-07 or 10-1-07 but when i try
to
use concatenate or $ Excel only displays the date value ie 39249 for
6/16/07.
So instead of getting 9/1/07 to 10/1/07 to display in cell i would get
39429 to 39459 or something to that effect. Any suggestions.

Also i am currently setting up formulas to display say 45 days before
completion date at =date(year(A1), month(A1)-1, day(A1)-15. However, this
doesn't seem totally accurate. Any suggestions.

Return Date using two dates and a word
 

Do you mean rounding or just backdate? So does any date lower than 15th goto
1st, any date above go to the 15th?

=IF(DAY(my_date_formula)<15,my_date_formula-DAY(my_date_formula)+1,my_date_formula-DAY(my_date_formula)+15)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"pblenis" wrote in message
...
Thanks Bob that did what i was looking for. Now i do have another issue i
just ran into. I need to make sure the values returned for completion
dates
always fall on a 1st or 15th (never mind if they are weekends its for
generic
reference). Is there a rounding function for months and days?
"Bob Phillips" wrote:

=TEXT(A1-3,"dd/mm/yy")&" - "&TEXT(A1-1,"dd/mm/yy")

second part

=DATE(YEAR(A1),MONTH(A1),DAY(A1)-45)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"pblenis" wrote in message
...
I am trying to make a time table based of a completion date A1 for
specific
tasks.

Some completion dates have a range of dates. Here is what i need to
do.
If
cell A1 is 12/1/07 i want a cell to return "9-1-07 to 10-1-07". I have
no
problem getting cells to return a simple 9-1-07 or 10-1-07 but when i
try
to
use concatenate or $ Excel only displays the date value ie 39249 for
6/16/07.
So instead of getting 9/1/07 to 10/1/07 to display in cell i would get
39429 to 39459 or something to that effect. Any suggestions.

Also i am currently setting up formulas to display say 45 days before
completion date at =date(year(A1), month(A1)-1, day(A1)-15. However,
this
doesn't seem totally accurate. Any suggestions.

Return Date using two dates and a word
 

I think i figured it out. Thanks again Bob

"Bob Phillips" wrote:

Do you mean rounding or just backdate? So does any date lower than 15th goto
1st, any date above go to the 15th?

=IF(DAY(my_date_formula)<15,my_date_formula-DAY(my_date_formula)+1,my_date_formula-DAY(my_date_formula)+15)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"pblenis" wrote in message
...
Thanks Bob that did what i was looking for. Now i do have another issue i
just ran into. I need to make sure the values returned for completion
dates
always fall on a 1st or 15th (never mind if they are weekends its for
generic
reference). Is there a rounding function for months and days?
"Bob Phillips" wrote:

=TEXT(A1-3,"dd/mm/yy")&" - "&TEXT(A1-1,"dd/mm/yy")

second part

=DATE(YEAR(A1),MONTH(A1),DAY(A1)-45)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"pblenis" wrote in message
...
I am trying to make a time table based of a completion date A1 for
specific
tasks.

Some completion dates have a range of dates. Here is what i need to
do.
If
cell A1 is 12/1/07 i want a cell to return "9-1-07 to 10-1-07". I have
no
problem getting cells to return a simple 9-1-07 or 10-1-07 but when i
try
to
use concatenate or $ Excel only displays the date value ie 39249 for
6/16/07.
So instead of getting 9/1/07 to 10/1/07 to display in cell i would get
39429 to 39459 or something to that effect. Any suggestions.

Also i am currently setting up formulas to display say 45 days before
completion date at =date(year(A1), month(A1)-1, day(A1)-15. However,
this
doesn't seem totally accurate. Any suggestions.