Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi need some help with Month() or MonthName() when it comes to handling dates that are crossing over from an old month into a new Month.
I've a list of dates say starts from 1 September 2019 12:00AM to 30 Septepmber 2019 23:59PM
here is my Data below : Data in both Weeks and Month Script are as followed:
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;-$#,##0.00';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY h:mm:ss[.fff] TT';
SET FirstWeekDay=0;
SET BrokenWeeks=0;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='en-SG';
SET CreateSearchIndexOnReload=1;
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';
Load
Closed Date,
month(Monthend(Date#([Closed Date],'DD\MM\YYYY hh:mm:ss'))) as Month,
num(Week(Date#([Closed Date],'DD/MM/YYY hh:mm:ss')),'00') as Week
From Sample.xlsx;
For some odd reason, the script is reading my 1st and 2nd Sep as AUG instead of SEP
First of the Week for me is Monday as shown in FirstWeekDay=0;
I've attached sample data below.
I see this is happening for border dates like 1st of Sep moves to Aug right?
it could be due to some conversion happening as I said due to timestamp or some issue because of time.
so maybe it will work to consider this as text(or type cast using text function),remove time part and do calculation on month