Invoice due date and february...
lupo666
I need to do is calculate some invoices due dates, ex.:
Invoice date: 25/04/2009
Invoice time: 60 days
Ivoice due date: 30/06/2009
and print both of the above dates in the invoice itself, which is
already generated by my database anyway...
Everything works fine, except when February is the middle: being of
only 28 days, the due date goes too far,ex.:
Invoice date: 25/01/2009
Invoice time: 60 days
Ivoice due date: 30/04/2009
while it should clearly be the end of March, not April...
I have tried and tried, to no avail... The code I currently use to
calculate due date is:
=DateSerial(Year([DATE]+[TIME]);Month([DATE]+[TIME])+1;0)
Any help will be greatly appreciated...
Ciao, Lupo
Allen Browne
=DateSerial(Year([invoice date]), Month([invoice date])+3, 0)
From your examples, I think that's what you want (i.e. day zero of 3 months
ahead of this month calculates as the last day of 2 months ahead.)
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"lupo666" <lup...@gmail.com> wrote in message
news:76bf4f10-347d-4393...@a26g2000yqn.googlegroups.com...
lupo666
(directly or indirectly) so many times, I would not know how to ever
thank you! The problem is that 60 days can also be 30, 90 or 120...
I tried your solution and it does not work (same result as mine, both
the one in the previous message and the one below). Also tried this:
=DateSerial(Year([PAGAMENTO_TIME]+[Data]);Month([PAGAMENTO_TIME])+1+
([PAGAMENTO_TIME]/30);0)
which also does not work...
I hope I don't have to do a countless series of IF THENs to trap
February among all the different cases...
Ciao, Lupo
On 6 Ago, 16:17, "Allen Browne" <AllenBro...@SeeSig.Invalid> wrote:
> Try:
> =DateSerial(Year([invoice date]), Month([invoice date])+3, 0)
>
> From your examples, I think that's what you want (i.e. day zero of 3 months
> ahead of this month calculates as the last day of 2 months ahead.)
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users -http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "lupo666" <lupo...@gmail.com> wrote in message
>
> news:76bf4f10-347d-4393...@a26g2000yqn.googlegroups.com...
>
>
>
> > Hi everybody, I am building a database for a client and one the things
> > I need to do is calculate some invoices due dates, ex.:
>
> > Invoice date: 25/04/2009
> > Invoice time: 60 days
> > Ivoice due date: 30/06/2009
>
> > and print both of the above dates in the invoice itself, which is
> > already generated by my database anyway...
>
> > Everything works fine, except when February is the middle: being of
> > only 28 days, the due date goes too far,ex.:
>
> > Invoice date: 25/01/2009
> > Invoice time: 60 days
> > Ivoice due date: 30/04/2009
>
> > while it should clearly be the end of March, not April...
>
> > I have tried and tried, to no avail... The code I currently use to
> > calculate due date is:
>
> > =DateSerial(Year([DATE]+[TIME]);Month([DATE]+[TIME])+1;0)
>
> > Any help will be greatly appreciated...
>
> > Ciao, Lupo- Nascondi testo citato
>
> - Mostra testo citato -
hbinc
Hi Lupo,
Have you tried this one:
Invoice due date = Invoice date + 60
HBInc.
lupo666
invoices: 30/60/90/120 means 30/60/90/120 days after invoice date to
the end of month, which means that an invoice issued on the 13 March
with 30 days due will be due on the 30th April (go to the end of the
issueing month, jump one month ahead...) and so forth...
Anyway, I am working on a couple of solutions which might just work,
who knows... I'll keep you posted... In case any other idea comes to
mind, please send...
Ciao, Lupo
> HBInc.- Hide quoted text -
>
> - Show quoted text -
AKN_39
Lupo: Would the following work for you:
InvoiceDueDate = DateAdd("m", InvoiceTime / 30, DateSerial(Year
(InvoiceDate), Month(InvoiceDate) + 1, 0))
It handles your sample dates, but I haven't tested for all cases.
Tony N.
hbinc
>
> - Show quoted text -
Hi Lupo,
In that case try:
30: DateSerial(Year(Invoice_date),Month(Invoice_date) + 2,1) - 1
60: DateSerial(Year(Invoice_date),Month(Invoice_date) + 3,1) - 1
etc.
Or in words: go to the first of month after the Invoice due date, and
go one day back.
Instead of a space I used an underscore: in that case you do not need
the square brackets.
HBInc.
Allen Browne
The expression gives the last day of the month, n months ahead.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"lupo666" <lup...@gmail.com> wrote in message
news:afe9ae8b-3fdf-4b7a...@s31g2000yqs.googlegroups.com...
>
> I tried your solution and it does not work (same result as mine,
> both the one in the previous message and the one below).
>>
lupo666
in a semi-simple way. I'll try to explain it. First some definitions:
PAGAMENTO_TIME: the number of days (months) given to the client to pay
(read my post on how complicated this is in Italy above...). Another
very good example is this: Invoice issued on the 10th of December
2008, with 60 days DFFM, will be due on the 28th of February 2009...
Iniziale: Issuing date
Febbraio: The february of the year of the due date
Finale: The due date with the february error...
SCADENZA: The correct due date...
These are the formulas I have used, in hidden text boxes, expect for
SCADENZA, which must be printed in the invoice:
Iniziale =DateSerial(Year([Data]);Month([Data])+1;0)
Febbraio =DateSerial(Year([Data]+[PAGAMENTO_TIME]);3;0)
Finale =DateSerial(Year([Data]+[PAGAMENTO_TIME]);Month([Data]+
[PAGAMENTO_TIME])+1;0)
SCADENZA =IIf([Iniziale]<[Febbraio] And [Finale]>[Febbraio];DateSerial
(Year([Finale]);Month([Finale]);0);DateSerial(Year([Finale]);Month
([Finale])+1;0))
Basically, I calculated the 28th of february of the due year and if it
was in the middle between issuing date and (maybe) wrong due date,
then subtracted a month from due date, otherwise not...
I have tested it, it works in all cases and I hope these formulas can
be useful to others. I also hope that invoicing in your countries is
not as complicated as it is in Italy...
Again, thank you all for your great help!
Ciao, Lupo
On 7 Ago, 06:07, "Allen Browne" <AllenBro...@SeeSig.Invalid> wrote:
> If it did not work, then I guess I don't understand what you want.
>
> The expression gives the last day of the month, n months ahead.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users -http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "lupo666" <lupo...@gmail.com> wrote in message
>
> news:afe9ae8b-3fdf-4b7a...@s31g2000yqs.googlegroups.com...
>
>
>
>
>
> > I tried your solution and it does not work (same result as mine,
> > both the one in the previous message and the one below).
>
> >> =DateSerial(Year([invoice date]), Month([invoice date])+3, 0)- Nascondi testo citato