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.
