Code for format two dates to September, 21 - 23, 2009

J

johndgraham

I have two dates, I would like to have formatted on my report as:

Month, startDay - endDate, year


I've put something together but its not quite right:
=Format([startdate],"mmmm") & " " & Format([startdate],"d") & "-" &
Format([enddate],"d") & ", " & Format([enddate],"yyyy")

This works with two exceptions:
1) when the two dates span a month, for example March 31 - 1, 2009
should be March 31 - April 1, 2009
2) When the dates are actually only one. for example March 31 - 31,
2009 should be March 31, 2009

Anyone have this already coded?
thanks,
John
 
J

John Spencer

You can use an nested IIF statement or you can use a custom VBA
function. The nested IIF solution has to account for 4 options
-- dates are equal
-- dates are same month but not equal day
-- dates are in different years
-- dates are different months but same year
(and possibly that neither date is there or one date is there).

Using a nested IIF statement (all on one line, broken up here for ease
of reading). Hopefully all the parentheses and parts are matched up
correctly.

IIF([StartDate]=[EndDate],Format([StartDate],"mmmm d, yyyy"
, IIF(Format([StartDate],"mmyy")=Format([EndDate],"mmyy")
, Format([StartDate],"mmmm d\-") & Format([EndDate],"d, yyyy")
, IIF(Year([StartDate])<>Year([EndDate])
, Format([StartDate],"mmmm d, yyyy\-") & Format([EndDate],"mmmm d,
yyyy")
, Format([StartDate],"mmmm d\-") & Format([EndDate],"d, yyyy")
)))

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top