Date Formula

[former_member550083]

HI Team,

Our SLA Cycle is 16th to 15th of every month e.g 16-01-2018 12:00:00 AM to 15-02-2018 11:59:59 PM, so customer wants whenever he extracts data from Webi then "Month Cycle" should be populated in report.

Example -

If He/She, pulled data for period from 16-01-2018 to 15-02-2018 then in Month column they wants like 16th January'2018 to 15th February 2018 instead of January & February

Regards,

Amit R. Lonkar

[amitrathi239]

are these dates coming in report with any object?

16-01-2018 12:00:00 AM to 15-02-2018 11:59:59 PM

[former_member550083]

Yes we have date field i.e "Submit Date"

[former_member550083]

Hi Amit,

Thanks for the help but its not giving me correct picture it shows #Multivalue.. please check

Regards,

Amit

9822301101.

[amitrathi239]

considering Submit date always have two dates with data type string use below variable.

=If(ToDate([Submit Date];"dd-MM-yyyy HH:mm:ss a")=Min(ToDate([Submit Date];"dd-MM-yyyy HH:mm:ss a") In Report)) Then FormatDate(ToDate([Submit Date];"dd-MM-yyyy HH:mm:ss a");"dd") +"th "+FormatDate(ToDate([Submit Date];"dd-MM-yyyy HH:mm:ss a");"Mmmm yyyy") ElseIf(ToDate([Submit Date];"dd-MM-yyyy HH:mm:ss a")=Max(ToDate([Submit Date];"dd-MM-yyyy HH:mm:ss a") In Report)) Then FormatDate(ToDate([Submit Date];"dd-MM-yyyy HH:mm:ss a");"dd") +"th "+FormatDate(ToDate([Submit Date];"dd-MM-yyyy HH:mm:ss a");"Mmmm yyyy")