28 days from end of date 1

[antheana]

Hi there,

I have a job table and am capturing the invoice date, payment terms (e.g. 28 days) and invoice due date. I have just found out that the payment terms are 28 days from the end of the week. I have:

JobPaymentDate =[JobPayment]+28

How can I get it to identify which day of the week it is and then work out the date of the Friday of that week and then add 28 days from then?

 

[MisterC]

Try this-
To get the end of the week for a date in cell C6:

=IF(WEEKDAY(C6)>6,C6+6,C6+6-WEEKDAY(C6))

Then just add 28 days to it to get your due date.

 

[antheana]

Hi there,

Thanks for your reply. I'm not sure if I know if there is a C6! I'm not using Excel, I'm using MS Access (have I posted in the wrong forum?)

Will this still work? If so, do I still write it the same way?

 

[SkipVought]

You're in the correct forum.

His forumla would be...
[tt]
IIF(WEEKDAY([Your Date])>6,[Your Date]+6,[Your Date]+6-WEEKDAY([Your Date]))
[/tt]
I'd suggest...
[tt]
INT([Your Date]/7)*7+6
[/tt]
gets you the next FRI.

then add 28.



Skip,

_[red][/red]

 

[antheana]

I SO don't have as much Access knowledge as I thought!

Where would I need to enter that? I've tried to put it in the JobPaymentDate field and it didn't like it.

Can I have the 'inserting code for dummies version?' please?

 

[SkipVought]

In the QBE Grid field. Let's say you dragged the field, [Your Date], into the grid. Just replace the field with the expression.

Skip,

_[red][/red]

 

[antheana]

I tried that and it seemed to not show 2 of my jobs.

I entered:

(Int([JobInvoiceDate]/7)*7+6)+28

Under the Criteria section of the JopPaymentDate field that I'm trying to work out

 

[SkipVought]

This is a criteria for WHAT field? For instance...

Where [b][WhatField][/b] < (Int([JobInvoiceDate]/7)*7+6)+28

and could the rows be excluded by the criteria???

Skip,

_[red][/red]

 

[antheana]

I ended up finding another solution. On the form, I went with:

AfterUpdate for the InvoiceDate field:

[JobPaymentDate] = [JobInvoiceDate] + 35 - Weekday([JobInvoiceDate])

This actually takes it from the Saturday, which is a better solution as for some jobs, Saturday is still a working day.

Thank you for your help anyway.

antheana

 

[willybgw]

Skip,

I liked your formula. Have a star.

willybgw