I am trying to enter a value in Cell B1, it is a combination of Date and
text, what I want it to say is

"Report Date Range:

then the date range should be the 1st of the previous month to the end of
the previous month in the format dd/mm/yyyy

any help is appreciated

thanks

="Report Date Range:
"&TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1),"dd/mm/yyyy")&"-"&TEXT(DATE(YEAR(TODAY()),MONTH(TODAY()),0),"d d/mm/yyyy")

--
---
HTH

Bob

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



"Nigel" wrote in message
...
I am trying to enter a value in Cell B1, it is a combination of Date and
text, what I want it to say is

"Report Date Range:

then the date range should be the 1st of the previous month to the end of
the previous month in the format dd/mm/yyyy

any help is appreciated

thanks

No it didn't like any of it

"Bob Phillips" wrote:

="Report Date Range:
"&TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1),"dd/mm/yyyy")&"-"&TEXT(DATE(YEAR(TODAY()),MONTH(TODAY()),0),"d d/mm/yyyy")

--
---
HTH

Bob

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



"Nigel" wrote in message
...
I am trying to enter a value in Cell B1, it is a combination of Date and
text, what I want it to say is

"Report Date Range:

then the date range should be the 1st of the previous month to the end of
the previous month in the format dd/mm/yyyy

any help is appreciated

thanks

Describe "no it didn't like any of it". In what way? When I entered Bob's
formula in B1 (actually, any cell), it gave me what you asked for.

Note: Bob's formula is a spreadsheet formula, not a VBA one.

Rick




"Nigel" wrote in message
...
No it didn't like any of it

"Bob Phillips" wrote:

="Report Date Range:
"&TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1),"dd/mm/yyyy")&"-"&TEXT(DATE(YEAR(TODAY()),MONTH(TODAY()),0),"d d/mm/yyyy")

--
---
HTH

Bob

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



"Nigel" wrote in message
...
I am trying to enter a value in Cell B1, it is a combination of Date and
text, what I want it to say is

"Report Date Range:

then the date range should be the 1st of the previous month to the end
of
the previous month in the format dd/mm/yyyy

any help is appreciated

thanks

AAh then that would be the problem, I am trying to get this done by
programming not as a spreadsheet formula


"Rick Rothstein (MVP - VB)" wrote:

Describe "no it didn't like any of it". In what way? When I entered Bob's
formula in B1 (actually, any cell), it gave me what you asked for.

Note: Bob's formula is a spreadsheet formula, not a VBA one.

Rick




"Nigel" wrote in message
...
No it didn't like any of it

"Bob Phillips" wrote:

="Report Date Range:
"&TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1),"dd/mm/yyyy")&"-"&TEXT(DATE(YEAR(TODAY()),MONTH(TODAY()),0),"d d/mm/yyyy")

--
---
HTH

Bob

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



"Nigel" wrote in message
...
I am trying to enter a value in Cell B1, it is a combination of Date and
text, what I want it to say is

"Report Date Range:

then the date range should be the 1st of the previous month to the end
of
the previous month in the format dd/mm/yyyy

any help is appreciated

thanks

bob's works fine here, too.

if you want vba for bob's solution, try this

With Range("B1")
..Formula = "=""Report Date Range:"" &
text(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1),""dd/mm/yyyy"")" _
& "& ""-""&TEXT(DATE(YEAR(TODAY()),MONTH(TODAY()),0)," "dd/mm/yyyy"")"
End With

or

With Range("B1")

..Value = "Report Date Range:" & Format(DateSerial(Year(Date), Month(Date) - 1,
1), "dd/mm/yyyy") & "-" & _
Format(DateSerial(Year(Date), Month(Date), 0), "dd/mm/yyyy")
--


Gary


"Nigel" wrote in message
...
No it didn't like any of it

"Bob Phillips" wrote:

="Report Date Range:
"&TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1),"dd/mm/yyyy")&"-"&TEXT(DATE(YEAR(TODAY()),MONTH(TODAY()),0),"d d/mm/yyyy")

--
---
HTH

Bob

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



"Nigel" wrote in message
...
I am trying to enter a value in Cell B1, it is a combination of Date and
text, what I want it to say is

"Report Date Range:

then the date range should be the 1st of the previous month to the end of
the previous month in the format dd/mm/yyyy

any help is appreciated

thanks

activesheet.range("B1").value _
= "Report Date Range: " & format(date-day(date),"dd/mm/yyyy")

or

With ActiveSheet.Range("B1")
.NumberFormat = """Report Date Range: ""dd/mm/yyyy"
.Value = Date - Day(Date)
End With

The second one keeps it a date so you can do further calculations.



Nigel wrote:

I am trying to enter a value in Cell B1, it is a combination of Date and
text, what I want it to say is

"Report Date Range:

then the date range should be the 1st of the previous month to the end of
the previous month in the format dd/mm/yyyy

any help is appreciated

thanks

--

Dave Peterson

i see it wrapped incorrectly

With Range("B1")
..Value = "Report Date Range:" & Format(DateSerial(Year(Date), Month(Date) - 1, _
1), "dd/mm/yyyy") & "-" & Format(DateSerial(Year(Date), Month(Date), 0), _
"dd/mm/yyyy")
End With

--


Gary


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
bob's works fine here, too.

if you want vba for bob's solution, try this

With Range("B1")
.Formula = "=""Report Date Range:"" &
text(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1),""dd/mm/yyyy"")" _
& "& ""-""&TEXT(DATE(YEAR(TODAY()),MONTH(TODAY()),0)," "dd/mm/yyyy"")"
End With

or

With Range("B1")

.Value = "Report Date Range:" & Format(DateSerial(Year(Date), Month(Date) - 1,
1), "dd/mm/yyyy") & "-" & _
Format(DateSerial(Year(Date), Month(Date), 0), "dd/mm/yyyy")
--


Gary


"Nigel" wrote in message
...
No it didn't like any of it

"Bob Phillips" wrote:

="Report Date Range:
"&TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1),"dd/mm/yyyy")&"-"&TEXT(DATE(YEAR(TODAY()),MONTH(TODAY()),0),"d d/mm/yyyy")

--
---
HTH

Bob

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



"Nigel" wrote in message
...
I am trying to enter a value in Cell B1, it is a combination of Date and
text, what I want it to say is

"Report Date Range:

then the date range should be the 1st of the previous month to the end of
the previous month in the format dd/mm/yyyy

any help is appreciated

thanks

Each one of you has provided a great response and I was able to make it work,

thanks so much



"Gary Keramidas" wrote:

bob's works fine here, too.

if you want vba for bob's solution, try this

With Range("B1")
..Formula = "=""Report Date Range:"" &
text(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1),""dd/mm/yyyy"")" _
& "& ""-""&TEXT(DATE(YEAR(TODAY()),MONTH(TODAY()),0)," "dd/mm/yyyy"")"
End With

or

With Range("B1")

..Value = "Report Date Range:" & Format(DateSerial(Year(Date), Month(Date) - 1,
1), "dd/mm/yyyy") & "-" & _
Format(DateSerial(Year(Date), Month(Date), 0), "dd/mm/yyyy")
--


Gary


"Nigel" wrote in message
...
No it didn't like any of it

"Bob Phillips" wrote:

="Report Date Range:
"&TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1),"dd/mm/yyyy")&"-"&TEXT(DATE(YEAR(TODAY()),MONTH(TODAY()),0),"d d/mm/yyyy")

--
---
HTH

Bob

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



"Nigel" wrote in message
...
I am trying to enter a value in Cell B1, it is a combination of Date and
text, what I want it to say is

"Report Date Range:

then the date range should be the 1st of the previous month to the end of
the previous month in the format dd/mm/yyyy

any help is appreciated

thanks

Then

Activecell.Value = "Report Date Range: " & _
Format(DateSerial(Year(Date),Month(Date)-1,1),"dd/mm/yyyy")
& "-" & _
Format(Date - Day(Date),"dd/mm/yyyy")



--
---
HTH

Bob

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



"Nigel" wrote in message
...
AAh then that would be the problem, I am trying to get this done by
programming not as a spreadsheet formula


"Rick Rothstein (MVP - VB)" wrote:

Describe "no it didn't like any of it". In what way? When I entered Bob's
formula in B1 (actually, any cell), it gave me what you asked for.

Note: Bob's formula is a spreadsheet formula, not a VBA one.

Rick




"Nigel" wrote in message
...
No it didn't like any of it

"Bob Phillips" wrote:

="Report Date Range:
"&TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1),"dd/mm/yyyy")&"-"&TEXT(DATE(YEAR(TODAY()),MONTH(TODAY()),0),"d d/mm/yyyy")

--
---
HTH

Bob

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



"Nigel" wrote in message
...
I am trying to enter a value in Cell B1, it is a combination of Date
and
text, what I want it to say is

"Report Date Range:

then the date range should be the 1st of the previous month to the
end
of
the previous month in the format dd/mm/yyyy

any help is appreciated

thanks

On Jul 10, 8:18 am, Nigel wrote:
AAh then that would be the problem, I am trying to get this done by
programming not as a spreadsheet formula

"Rick Rothstein (MVP - VB)" wrote:

Describe "no it didn't like any of it". In what way? When I entered Bob's
formula in B1 (actually, any cell), it gave me what you asked for.

Note: Bob's formula is a spreadsheet formula, not a VBA one.

Rick

"Nigel" wrote in message
...
No it didn't like any of it

"Bob Phillips" wrote:

="Report Date Range:
"&TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1),"dd/mm/yyyy")&"-"&TEXT(DATE(YEAR(TODAY()),MONTH(TODAY()),0),"d d/mm/yyyy")

--
---
HTH

Bob

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

"Nigel" wrote in message
...
I am trying to enter a value in Cell B1, it is a combination of Date and
text, what I want it to say is

"Report Date Range:

then the date range should be the 1st of the previous month to the end
of
the previous month in the format dd/mm/yyyy

any help is appreciated

thanks

Sub Test()
Range("B2") = "Report Date Range: " & Format(DateSerial(Year(Date),
Month(Date) - 1, 1), "dd/mm/yyyy") & "-" &
Format(DateSerial(Year(Date), Month(Date), 0), "dd/mm/yyyy")
End Sub

This will work for vba. It's pretty much bob's formula translated
into VBA

Then

Activecell.Value = "Report Date Range: " & _

Format(DateSerial(Year(Date),Month(Date)-1,1),"dd/mm/yyyy") & "-" &
Format(Date - Day(Date),"dd/mm/yyyy")

Just like you used this for the last day of the previous month...

Date - Day(Date)

you could have used this for its first day...

Date - Day(Date) - Day(Date - Day(Date)) + 1

instead of the DateSerial construction. Probably no real advantage in doing
so, but it looks "cleaner" to me for some reason.

Rick

I didn't notice that you wanted the first of the previous month.

You still may want to look at using a custom numberformat with that cell.

Dave Peterson wrote:

activesheet.range("B1").value _
= "Report Date Range: " & format(date-day(date),"dd/mm/yyyy")

or

With ActiveSheet.Range("B1")
.NumberFormat = """Report Date Range: ""dd/mm/yyyy"
.Value = Date - Day(Date)
End With

The second one keeps it a date so you can do further calculations.

Nigel wrote:

I am trying to enter a value in Cell B1, it is a combination of Date and
text, what I want it to say is

"Report Date Range:

then the date range should be the 1st of the previous month to the end of
the previous month in the format dd/mm/yyyy

any help is appreciated

thanks

--

Dave Peterson

--

Dave Peterson

Yeah, I think I buy that one <g

--
---
HTH

Bob

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



"Rick Rothstein (MVP - VB)" wrote in
message ...
Then

Activecell.Value = "Report Date Range: " & _

Format(DateSerial(Year(Date),Month(Date)-1,1),"dd/mm/yyyy") & "-" &
Format(Date - Day(Date),"dd/mm/yyyy")

Just like you used this for the last day of the previous month...

Date - Day(Date)

you could have used this for its first day...

Date - Day(Date) - Day(Date - Day(Date)) + 1

instead of the DateSerial construction. Probably no real advantage in
doing so, but it looks "cleaner" to me for some reason.

Rick