x Want to participate? Join the group to interact
  • State Verified Answer
  • Replies 5 replies
  • Subscribers 13 subscribers
  • Views 1429 views
  • Users 0 members are here
Browse Forums
Announcements
105
topics
4
replies
General Discussion
1k
topics
2k
replies
Geoff Turner

Report designer: Expression for number of days between dates

SOLVED
Posted By

Does anyone know how to get the number of days between 2 dates, as an integer? Just doing (Date1 - Date2) gives a timespan; I need a number!

I'm trying to show the average daily sales of a stock item. I have an expression PeriodSalesQty, and a date criteria ThisPeriod. The expression DaysInPeriod=
CRITERIA.ThisPeriod_UNDERLYINGVALUE_TO - CRITERIA.ThisPeriod_UNDERLYINGVALUE_FROM +1
shows the correct number of days, but the expression
PeriodSalesQty /DaysInPeriod
gives the error "/ is not defined for objects of type System.Double and System.Timespan"

How do I convert the Timespan to a number of days?

Parents
  • 0

    there is probably an easier way, but this should work.  turn it to a string, get the first part of the string, and turn that to decimal, then do the maths


    PeriodSalesQty /
    (stringtofloat(substring(cstring(CRITERIA.ThisPeriod_UNDERLYINGVALUE_TO - (CRITERIA.ThisPeriod_UNDERLYINGVALUE_FROM +1)),0,IndexOf(cstring(CRITERIA.ThisPeriod_UNDERLYINGVALUE_TO - (CRITERIA.ThisPeriod_UNDERLYINGVALUE_FROM +1)),"."))))

  • +1 in reply to Toby
    verified answer

    I came up with similar:

    DaysInPeriod = StringToInteger(FormatString("{0:%d}",CRITERIA.ThisPeriod_UNDERLYINGVALUE_TO - CRITERIA.ThisPeriod_UNDERLYINGVALUE_FROM +1))

  • 0 in reply to Geoff Turner

    Ahhh much more elegant than mine

  • 0 in reply to Toby

    I just hope the customer doesn't ask for the number of weekdays!

  • 0 in reply to Geoff Turner

    This is a way of getting the number of weekdays between 2 dates "StartDate" and "EndDate"

    Add the expression WholeWeeks = StringToInteger ( FormatString("{0:%d}",EndDate- StartDate)) / 7, which gives the number of whole weeks between startdate and enddate

    Add the expression StartEndDay = FormatString("{0:ddd}-{1:ddd} ",StartDate,EndDate), which is the days of the week of the start and end dates (eg "Mon-Sat")

    Add this lovely expression ExtraDays =

    Contains("Sat-Sat Sat-Sun Sun-Sun Tue-Mon Wed-Tue Thu-Wed Fri-Thu ",StartEndDay)?0:
    Contains("Sat-Mon Sun-Mon Mon-Mon Tue-Tue Wed-Wed Thu-Thu Fri-Fri Fri-Sat Fri-Sun ",StartEndDay)?1:
    Contains("Sat-Tue Sun-Tue Mon-Tue Tue-Wed Wed-Thu Thu-Fri Thu-Sat Thu-Sun ",StartEndDay)?2:
    Contains("Sat-Wed Sun-Wed Mon-Wed Tue-Thu Wed-Fri Wed-Sat Wed-Sun ",StartEndDay)?3:
    Contains("Sat-Thu Sun-Thu Mon-Thu Tue-Fri Tue-Sat Tue-Sun ",StartEndDay)?4:5

    which gives the number days in the part weeks; eg "Mon-Mon" is 1 day, "Mon-Tue" is 2 days etc

    Finally, Weekdays = WholeWeeks * 5 + ExtraDays

    E&OE!

Reply
  • 0 in reply to Geoff Turner

    This is a way of getting the number of weekdays between 2 dates "StartDate" and "EndDate"

    Add the expression WholeWeeks = StringToInteger ( FormatString("{0:%d}",EndDate- StartDate)) / 7, which gives the number of whole weeks between startdate and enddate

    Add the expression StartEndDay = FormatString("{0:ddd}-{1:ddd} ",StartDate,EndDate), which is the days of the week of the start and end dates (eg "Mon-Sat")

    Add this lovely expression ExtraDays =

    Contains("Sat-Sat Sat-Sun Sun-Sun Tue-Mon Wed-Tue Thu-Wed Fri-Thu ",StartEndDay)?0:
    Contains("Sat-Mon Sun-Mon Mon-Mon Tue-Tue Wed-Wed Thu-Thu Fri-Fri Fri-Sat Fri-Sun ",StartEndDay)?1:
    Contains("Sat-Tue Sun-Tue Mon-Tue Tue-Wed Wed-Thu Thu-Fri Thu-Sat Thu-Sun ",StartEndDay)?2:
    Contains("Sat-Wed Sun-Wed Mon-Wed Tue-Thu Wed-Fri Wed-Sat Wed-Sun ",StartEndDay)?3:
    Contains("Sat-Thu Sun-Thu Mon-Thu Tue-Fri Tue-Sat Tue-Sun ",StartEndDay)?4:5

    which gives the number days in the part weeks; eg "Mon-Mon" is 1 day, "Mon-Tue" is 2 days etc

    Finally, Weekdays = WholeWeeks * 5 + ExtraDays

    E&OE!

Children
No Data

*Community Hub is the new name for Sage City