Custom dates for a month

Hi,

I am having two columns, one is date other is sales. I have to find monthly sales but a month is to be considered as 24th of last month to 25 of the present month.

For example: Sales for May would be from 24th April to 25th May.

Anyone who could suggest me a formula/tool for this? Thanks in advance

Utkarsh

Date Time

Accepted answers

MarqueeCrew

@agrawaluk,

I think that your dates are backwards. The 25th of last month to the 24th of this month would avoid double counting the 24th.

Use the following formula to create a business month column as STRING (7):

IF ToNumber(Right([Date],2)) > 24 THEN
	DateTimeFormat(DateTimeAdd([Date],7,"days"),"%Y-%m")
ELSE
	DateTimeFormat([Date],"%Y-%m")
ENDIF

You'll get YYYY-MM as the output. Now you can summarize by grouping on the business month.

Cheers,

Mark

All comments

Thableaus

Hi @agrawaluk

So just to be clear, 24th of May would appear in two months? (April to May and May to June)

Cheers,

agrawaluk

Hi @thableaus

Yes, that's right!

MarqueeCrew

@agrawaluk,

I think that your dates are backwards. The 25th of last month to the 24th of this month would avoid double counting the 24th.

Use the following formula to create a business month column as STRING (7):

IF ToNumber(Right([Date],2)) > 24 THEN
	DateTimeFormat(DateTimeAdd([Date],7,"days"),"%Y-%m")
ELSE
	DateTimeFormat([Date],"%Y-%m")
ENDIF

You'll get YYYY-MM as the output. Now you can summarize by grouping on the business month.

Cheers,

Mark

agrawaluk

This is exactly what I was looking for. Thanks @marqueecrew

Apologies for previous misleading post.

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335