Excel/Access formula that determines month by date

Gilberto Sanches
Gilberto Sanches used Ask the Experts™
on
A company uses for reports the period from the 26th of the last month until the 25th of the new month. Example: November 2019 : 26 Oct - 25 Nov

That is not the standard period application are used to. Meaning in applications/ formula the month November runs from the 1st to the 30th.

I need a formula that puts the dates, like Dec 12 2019 automatically in the period December 2019.
And Dec, 27 2019 to period January 2020. How can we accomplish this?

In other situations I covered the days in the month based with the weeks. For example month December has weeks 49, 50, 51 & 52. But in this situation it needs to be more granular.

First I need a formula for Excel & later on also for Access.
How can we solve this with a formula?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You can use this expression:

MonthPeriod = Format(DateAdd("m", 1, DateAdd("d", -25, [YourDate])), "mmmm yyyy")

Open in new window

Gilberto SanchesFreelance ICT Consultant

Author

Commented:
Thanks Gustav for helping out. If I have it right that expression is for Access, correct?
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Yes. It will work in Excel as well.
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Gilberto SanchesFreelance ICT Consultant

Author

Commented:
Gustav, how do I apply the expression you shared?

When I put the expression
MonthPeriod = Format(DateAdd("m", 1, DateAdd("d", -25, [datum_F] )), "mmmm yyyy") 

Open in new window

in a new field Access 2016 gives error "The expression cannot be used in Calculated Column"
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Use a query. Calculated fields should be avoided if at all possible.
Gilberto SanchesFreelance ICT Consultant

Author

Commented:
Alright, it's obvious I don't much of this. How should this be put in a Access query?
I created a new field then In design view I put for that field as default value
MonthPeriod"=Format(DateAdd("m",1,DateAdd("d",-25,[datum_F])),"mmmm yyyy")

Open in new window

Then I get error " The database engine doesn't recognize either the field "datum_F"  in a valid expression, or the default value in the table "Logging Register"
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
In Excel, you can get the period as a date using:
=DATE(YEAR(A2),MONTH(A2)+(DAY(A2)>25),1)

Open in new window


And as text like December 2019:
=TEXT(DATE(YEAR(A2),MONTH(A2)+(DAY(A2)>25),1),"mmmm yyyy")

Open in new window

Gilberto SanchesFreelance ICT Consultant

Author

Commented:
You guys are so amazing. Byundt, your Excel formula works! Thank you so much.

I wonder how this can be done in Access 2016 & Excel's Power Query.
John TsioumprisSoftware & Systems Engineer

Commented:
Try this variation
MonthPeriod = IIf(Day([dateField])>25,Month([dateField]),Month([dateField])-1)

Open in new window

or (if ; is the system delimiter)
MonthPeriod = IIf(Day([dateField])>25;Month([dateField]);Month([dateField])-1)

Open in new window

Gilberto SanchesFreelance ICT Consultant

Author

Commented:
@John, try in Excel Power Query or in Access 2016 Expression Builder?
Gilberto SanchesFreelance ICT Consultant

Author

Commented:
@ John,
In Access 2016 the Expression Builder for Calculated Column gives the following
1)
MonthPeriod = IIf(Day( [datum_F] )>25,Month( [datum_F] ),Month( [datum_F] )-1)

Open in new window

Gives error  'Could not find field 'MonthPeriod'

When I create a field with that name the formula is accepted, a new field gets created with name Field1 and both (MonthPeriod & Field1) have no data in it.

2)
MonthPeriod = IIf(Day( [datum_F] )>25;Month( [datum_F] );Month( [datum_F] )-1)

Open in new window

Gives error: The Expression you entered contains invalid syntax. You omitted and operand or operator, you entered an invalid character or comma, or you entered text without surrounding it in quotation marks.
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
I created a new field then In design view I put for that field as default value

It is not a default but a calculated value. Thus, in Access, use a query like:

Select 
    *,
    Format(DateAdd("m", 1, DateAdd("d", -25, [datum_F])), "mmmm yyyy") As MonthPeriod
From
    YourTable

Open in new window

Or, might be preferable, move the formatting to the textbox where you display the value:

Select 
    *,
    DateAdd("m", 1, DateAdd("d", -25, [datum_F])) As MonthPeriod
From
    YourTable

Open in new window

Then set the Format property of the textbox to: mmmm yyyy
Gilberto SanchesFreelance ICT Consultant

Author

Commented:
Hey Gustav, thanks for responding. I know you are a expert at this but I can't seem to apply your instruction. Where do I apply this formula (step by step)? I feel confused with select query and a calculated field.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Open a new query, switch to SQL view, copy-paste code, adjust it to use your actual table and field names.
Gilberto SanchesFreelance ICT Consultant

Author

Commented:
WoW, Gustav! The SQL code
Select 
    *,
    Format(DateAdd("m", 1, DateAdd("d", -25, [datum_F])), "mmmm yyyy") As MonthPeriod
From
    YourTable

Open in new window

works as expected.

I even added another column to the code.
And made this of it
Select 
    *,
    Format(DateAdd("m", 1, DateAdd("d", -25, [datum_F])), "mmmm yyyy") As ProductionPeriod_F,
    Format(DateAdd("m", 1, DateAdd("d", -25, [datum_S])), "mmmm yyyy") As ProductionPeriod_S
From
    Logging_Register

   

Open in new window

This is so amazing.
What resources would you recommend to a  beginner like me to learn about these SQL codes?
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Great!
There many guides online for the browsing. I learned from trial and error and the old Access manuals from version 1.0 to Access 97.

A year ago or so I co-authored for Microsoft a guide covering the one type that the GUI won't create:
Use a union query to combine multiple queries into a single result
Gilberto SanchesFreelance ICT Consultant

Author

Commented:
Okay Gustav, thanks for sharing the article. I'll study it.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial