Format Concatenated Date field

[geospatial]

I am trying to create a report where it creates student certificates for completion of a course. The issue I have is formatting the date of the courses. In the course table I have 2 columns (start and end date) They are in format mm/dd/yyyy. On our current certificates the date format is 10 - 21 July 2017 if the dates are in the same month and 21 July - 15 August 2017 if the dates fall in different months. How can I take the dates I have and format it as stated?

 

[Joe4]

What if they fall in different years?
Like December 15, 2016 and January 14, 2017?
What would you want those to look like?

Also, for days that are less than 10, do you want it to show a leading 0 for the day ("5" versus "05")?

 

[Joe4]

Based on my previous questions:
- if you want leading zeroes in front of single digit days
- if you want the year included in both parts if the years are different
then assuming for start and end dates in cells A1 and B1, this formula should return what you want:

=IF(TEXT(A1,"mmyy")=TEXT(B1,"mmyy"),TEXT(A1,"d") & " - " & TEXT(B1,"dd mmmm yyyy"),IF(YEAR(A1)=YEAR(B1),TEXT(A1,"dd mmmm") & " - " & TEXT(B1,"dd mmmm yyyy"),TEXT(A1,"dd mmmm yyyy") & " - " & TEXT(B1,"dd mmmm yyyy")))

 

[Joe4]

Sorry, I missed that you posted this in the Access section!

In Access, if your two date fields were Date1 and Date2, then try this:

[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]IIF(FORMAT(Date1,"mmyy")=FORMAT(Date2,"mmyy"),FORMAT(Date1,"dd")  & " - " & FORMAT(Date2,"dd mmmm  yyyy"),IIF(YEAR(Date1)=YEAR(Date2),FORMAT(Date1,"dd mmmm")  & " - " & FORMAT(Date2,"dd mmmm  yyyy"),FORMAT(Date1,"dd mmmm yyyy") & " - "  & FORMAT(Date2,"dd mmmm yyyy")))[/TD]
[/TR]
</tbody>[/TABLE]

 

[geospatial]

Sorry, I missed that you posted this in the Access section!

In Access, if your two date fields were Date1 and Date2, then try this:

[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]IIF(FORMAT(Date1,"mmyy")=FORMAT(Date2,"mmyy"),FORMAT(Date1,"dd")  & " - " & FORMAT(Date2,"dd mmmm  yyyy"),IIF(YEAR(Date1)=YEAR(Date2),FORMAT(Date1,"dd mmmm")  & " - " & FORMAT(Date2,"dd mmmm  yyyy"),FORMAT(Date1,"dd mmmm yyyy") & " - "  & FORMAT(Date2,"dd mmmm yyyy")))
[/TD]
[/TR]
</tbody>[/TABLE]

Thanks,

I will give it a try. The dates will always be in the same year. Since we have a lot of holidays in December we do a schedule break over the new years and pickup classes back again at the end of January.

 

[Joe4]

OK. Then we can simplify the formula a bit:

IIf(Format([Date1],"mmyy")=Format([Date2],"mmyy"),Format([Date1],"d") & " - " & Format([Date2],"dd mmmm yyyy"), Format([Date1],"dd mmmm") & " - " & Format([Date2],"dd mmmm yyyy"))

 

[geospatial]

That worked. Much better than the solution I was going with. I had seperated out the day and month for both the start date and end date and then separated out the year as well. Created 2 concat statements and then was going to use a combo box on the form to ask if the dates went across 2 months and then if it was yes/no that would choose the statement.

Thank you very much.

 

[Joe4]

You are welcome.
Glad I was able to help!

 

[geospatial]

One last question, and I can start a new thread if need be.

Your solution worked great, and I did that in a query. In my report though I can only seem to add fields from a table and not a query. How do I add fields from a query?

 

[Joe4]

You have a few options:

1. Reports can be based on Tables OR Queries. If you like to do you calculations in Queries (and this is what I typically prefer), if you do it in the Query, and then use that Query as the Record Source of your Report, then those calculated fields in your Query will be available to you.

2. If you base the Report directly on the Table (i.e. the Table is your Record Source), then you could add a blank Text Box to your Report, and enter the calculation formula into the Control Source of that Text Box (as long as you show the two individual dates on the Report).

I prefer option 1. This also allows you to easy filter the records appearing on your report and add more calculations (via the Query), should you need to, down the road.