Place Value in Cell

[Guest]

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

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

--
---
HTH

Bob

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

 

[Guest]

No it didn't like any of it

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

--
---
HTH

Bob

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Guest]

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

 

[Gary Keramidas]

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

 

[Dave Peterson]

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.

 

[Gary Keramidas]

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

 

[Guest]

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

thanks so much

 

[Bob Phillips]

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)

 

[Mike]

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

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

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

 

[Bob Phillips]

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)