Discussions

Expand all | Collapse all

Please assist with date formula?

  • 1.  Please assist with date formula?

    Posted 04-12-2018 20:00
    The formula below the line works fine.  Now, I need to add the other scenario which is:
    If the customer name is NVR and ifinvoice is submitted within the week (Friday being the last day), payments for those submittals will be paid 30 days after the friday submittal deadline....

    If([Customer Name],"nvr"),                                         
       FirstDayofPeriod([DW 1 BILLED],Weeks(1),Date(2018,4,6))+Days(30),
    _____________________________________________
    var date BillingDate = [DW 1 BILLED];
    var date SameDayNextMonth = (AdjustMonth($BillingDate,1));

    If(Day($BillingDate) <= 15, Date(Year($SameDayNextMonth), Month($SameDayNextMonth),10),
                                               Date(Year($SameDayNextMonth), Month($SameDayNextMonth),25),


  • 2.  RE: Please assist with date formula?

    Top
    Contributor
    Posted 04-13-2018 14:51
    in order to have a 'rolling' formula that says 'within the week', we need to know the two dates.

    If one date is [DW 1 BILLED]  what is the other date that needs to 'be within the week' of?


  • 3.  RE: Please assist with date formula?

    Posted 04-13-2018 15:06
    It is the same field for both customers [DW 1 BILLED]. That is why I need an IF [Customer Name] is customer 1, then....use date formula 1,
    Otherwise, use date formula 2


  • 4.  RE: Please assist with date formula?

    Posted 05-09-2018 17:53

    This works fine for Customer 1 [SM JOB]

    var date BillingDate = [DW 1 BILLED];

    var date SameDayNextMonth = (AdjustMonth($BillingDate,1));

     

    If(Day($BillingDate) <= 15, Date(Year($SameDayNextMonth), Month($SameDayNextMonth),10),

                                               Date(Year($SameDayNextMonth), Month($SameDayNextMonth),25))

    Now, I need a different date result for Customer 2

    IF([NVR JOB]=true,

    ...the return date needs to be 30 days after the billing date.

    Please advise...I cannot seem to get () in correct places.  thx



  • 5.  RE: Please assist with date formula?

    Top
    Contributor
    Posted 05-09-2018 18:35
    I think you will need to use something like

    If([NVR Job]=true, $BillingDate+Days(30), ......)


  • 6.  RE: Please assist with date formula?

    Posted 05-09-2018 18:56
    Thank you.  Seems to be working. Now that this works, can we add the following:
    ...the actual return date should be on a Friday.
    For example: 
       Current return date is on  Wed., 6/6/18 (but could be on any day within the week)...so final return date should be Fri., 6/8


  • 7.  RE: Please assist with date formula?

    Posted 05-09-2018 19:01
    Disregard.  I have resolved the Friday issue.  thx


  • 8.  RE: Please assist with date formula?

    Top
    Contributor
    Posted 05-09-2018 19:01
    NextDayOfWeek (Date d, Number n)

    Description: Returns the first day after the given date d that falls on the weekday n.

    n is a number from 0 to 6 with Sunday being 0, Monday being 1, Tuesday being 2, and so on.

    Example: NextDayOfWeek([Date Submitted], 2) returns the first Tuesday that follows the Date in the Date Submitted field.