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

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

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

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

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 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