Select Date based on date value

  • Thread starter Thread starter salooha
  • Start date Start date
S

salooha

Is there a formula where I could select a date based on a condition?

here is a scenario of what I want the formula to do:

I have 3 dates:

start date,end date, and invoice date, let's assume that the start dat
is 9/7/2005, end date is 10/6/2005 and invoice date is 10/8/2005. no
even though the invoice was sent on 10/8/2005, I would like to identif
this month as september because the bulk of the month fell on september
I guess the rule would be(per example above):

per example above:
if starting month >= 09/20 then select 10/2005
else month < 9/20 then select 9/2005

so if the 20th of the month in the starting date passed then displa
the follwoing month, if not display current month. this will hel
identify the months not based on the invoice date but based on th
month that had the bulk of the activity.

I hope this make sense, please post reply or email me a
(e-mail address removed)

thanks a lot for your help...

sala
 
Is there a formula where I could select a date based on a condition?

here is a scenario of what I want the formula to do:

I have 3 dates:

start date,end date, and invoice date, let's assume that the start date
is 9/7/2005, end date is 10/6/2005 and invoice date is 10/8/2005. now
even though the invoice was sent on 10/8/2005, I would like to identify
this month as september because the bulk of the month fell on september.
I guess the rule would be(per example above):

per example above:
if starting month >= 09/20 then select 10/2005
else month < 9/20 then select 9/2005

so if the 20th of the month in the starting date passed then display
the follwoing month, if not display current month. this will help
identify the months not based on the invoice date but based on the
month that had the bulk of the activity.

I hope this make sense, please post reply or email me at
(e-mail address removed)

thanks a lot for your help...

salah
Click to expand...


=TEXT(DATE(YEAR(StartDate),MONTH(StartDate)
+(DAY(StartDate)>=20),1),"mm/yyyy")

or, if you need to do calculations on this date:

=DATE(YEAR(StartDate),MONTH(StartDate)+(DAY(StartDate)>=20),1)

and format the cell as mm/yyyy
--ron
 
Hi

One way
=Date(Year(A1),Month(A1)+(--(Day(a1)>=20),1)
Format>Cells>Number>Custom mm/yyyy


Regards

Roger Govier
 
Thanks a lot Guys...

here is what I came up with also, which one of the responses was clos
to it....


Code
-------------------
=+IF(DAY(B2)>=20,MONTH(C2),MONTH(B2)
-------------------


I appreciate your help..

thanks,
sala
 
Given that it's an invoice problem, (and invoices must be uniquely numbered)
there's a commercially practical solution which was used in my company,
which had cutoff dates for sales which were NOT month-end, and, daily
invoicing ... thousands of invoicesw every month.

It was the INVOICE number system which determined which month the sales were
in, and therefore may help you (and, the number system cut off/restarted,
monthly close)
You seem to be billing daily services ("activities") on a monthly basis, and
defining specific dates within a month as, in effect, cutoff dates - For
example - if Sept cutoff date was 15th (16 days in Aug, 15 in Sept - then
(with 2 days later invoicing), your INVOICE cutoff for August would be Sept
17th.
Ergo, invoices for Aug activity prefix 08-0001 onwards. For Sept activity,
invoicing in Oct, invoice no 10-0001 onwards, etc etc.
Simple matter, then, to collate sales data by month, based on the invoice no
prefix ....
 
Back
Top