# Excel/Access formula that determines month by date

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® 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")
``````
Freelance ICT Consultant

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.
Freelance ICT Consultant

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")
``````
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.
Freelance ICT Consultant

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

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

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.
Software & Systems Engineer

Commented:
Try this variation
``````MonthPeriod = IIf(Day([dateField])>25,Month([dateField]),Month([dateField])-1)
``````
or (if ; is the system delimiter)
``````MonthPeriod = IIf(Day([dateField])>25;Month([dateField]);Month([dateField])-1)
``````
Freelance ICT Consultant

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

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)
``````
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)
``````
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
*,
From
YourTable
``````
Or, might be preferable, move the formatting to the textbox where you display the value:

``````Select
*,
From
YourTable
``````
Then set the Format property of the textbox to: mmmm yyyy
Freelance ICT Consultant

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.
Freelance ICT Consultant

Commented:
WoW, Gustav! The SQL code
``````Select
*,
From
YourTable
``````
works as expected.

I even added another column to the code.
``````Select
*,
From
Logging_Register

``````
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
Freelance ICT Consultant

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

Do more with