Please assist with date formula?

  • 0
  • 1
  • Question
  • Updated 7 months ago
  • In Progress
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),
Photo of Lynne8817

Lynne8817

  • 410 Points 250 badge 2x thumb

Posted 8 months ago

  • 0
  • 1
Photo of Matthew Neil

Matthew Neil

  • 31,478 Points 20k badge 2x thumb
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?
Photo of Lynne8817

Lynne8817

  • 410 Points 250 badge 2x thumb
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
Photo of Lynne8817

Lynne8817

  • 410 Points 250 badge 2x thumb

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

Photo of Matthew Neil

Matthew Neil

  • 31,478 Points 20k badge 2x thumb
I think you will need to use something like

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

Lynne8817

  • 410 Points 250 badge 2x thumb
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
Photo of Lynne8817

Lynne8817

  • 410 Points 250 badge 2x thumb
Disregard.  I have resolved the Friday issue.  thx
Photo of Matthew Neil

Matthew Neil

  • 31,478 Points 20k badge 2x thumb
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.