asked on

Excel/Access formula that determines month by date

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?

Gustav Brock

You can use this expression:

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

Open in new window

Gilberto Sanches

ASKER

Thanks Gustav for helping out. If I have it right that expression is for Access, correct?

Gustav Brock

Yes. It will work in Excel as well.

Gilberto Sanches

ASKER

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"

Gustav Brock

Use a query. Calculated fields should be avoided if at all possible.

Gilberto Sanches

ASKER

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"

byundt

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 Sanches

ASKER

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 Tsioumpris

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 Sanches

ASKER

@John, try in Excel Power Query or in Access 2016 Expression Builder?

Gilberto Sanches

ASKER

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

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Gilberto Sanches

ASKER

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.

Gustav Brock

Open a new query, switch to SQL view, copy-paste code, adjust it to use your actual table and field names.

Gilberto Sanches

ASKER

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?

Gustav Brock

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 Sanches

ASKER

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