Date calculations

  • Thread starter Thread starter Barry
  • Start date Start date
B

Barry

I'm looking for a way to calculate if a date is between to other dates then
one thing, if not another thing.

The situation is I rent a cabin year round. The pricing between Jan. 1 and
May 25 is one Price, the rest of the time the Pricing is another .

Gourd and Bob I knew I'd be back soon... LOL
 
I'm looking for a way to calculate if a date is between to other dates then
one thing, if not another thing.

The situation is I rent a cabin year round. The pricing between Jan. 1 and
May 25 is one Price, the rest of the time the Pricing is another .

Gourd and Bob I knew I'd be back soon... LOL
Click to expand...

Really need some more information, but as a first start, with your rental date
in A1, the following should give you what you want:

=IF(AND(A1>=DATE(YEAR(A1),1,1),A1<=DATE(YEAR(A1),5,25)),"Pricing 1","Pricing2")


--ron
 
Try:

=IF(G3-DATE(YEAR(G3),1,1)>144,"Rate2","Rate1")

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Ron What information would you need?
Click to expand...

The formula I gave will work for computing the rate if the rate is determined
by the first day of the rental. But you may have some issues if the
determination is on a daily basis, and the rental includes dates with both
rates.
--ron
 
Ron wrote
The formula I gave will work for computing the rate if the rate is
determined
by the first day of the rental. But you may have some issues if the
determination is on a daily basis, and the rental includes dates with both
rates.
--ron
Click to expand...

Ron you're absolutely right, and I thought of that. I just didn't know how
to fix it. I was going to decide to use the begining/start date for the
calculation. If there is another way to include the calculation you mention
I'll be happy to post the workbook somewhere for you folks to view and
modify, as I'm not interested in selling it or anything.

Just name the place



Barry
 
Ron you're absolutely right, and I thought of that. I just didn't know how
to fix it. I was going to decide to use the begining/start date for the
calculation. If there is another way to include the calculation you mention
I'll be happy to post the workbook somewhere for you folks to view and
modify, as I'm not interested in selling it or anything.

Just name the place



Barry
Click to expand...


There may be simpler methods, but:

=SUM(IF((ROW(INDIRECT(StartDt&":"&EndDt))>=
DATE(YEAR(ROW(INDIRECT(StartDt&":"&EndDt))),1,1))*
(ROW(INDIRECT(StartDt&":"&EndDt))<=
DATE(YEAR(ROW(INDIRECT(StartDt&":"&EndDt))),5,25)),Rate1,Rate2))

entered as an **ARRAY** formula is one way to compute this.

To enter the ARRAY formula, after copying or entering the formula into the
cell, hold down <ctrl><shift> while hitting <enter>. If you did this
correctly, Excel will place braces {...} around the formula.

Using NAME'd cells for StartDt, EndDt, Rate1 and Rate2 would obviously help
readability.
--ron
 
Ron Rosenfeld said:
There may be simpler methods, but:

=SUM(IF((ROW(INDIRECT(StartDt&":"&EndDt))>=
DATE(YEAR(ROW(INDIRECT(StartDt&":"&EndDt))),1,1))*
(ROW(INDIRECT(StartDt&":"&EndDt))<=
DATE(YEAR(ROW(INDIRECT(StartDt&":"&EndDt))),5,25)),Rate1,Rate2))

entered as an **ARRAY** formula is one way to compute this.

To enter the ARRAY formula, after copying or entering the formula into the
cell, hold down <ctrl><shift> while hitting <enter>. If you did this
correctly, Excel will place braces {...} around the formula.

Using NAME'd cells for StartDt, EndDt, Rate1 and Rate2 would obviously
help
readability.
--ron
Click to expand...

I honestly appreciate your efforts and will try to apply them and let you
folks know how it came out.
I should say if I could post this file to some site for those of you to
view, I think you would be able to see my situation clearer.

So with that said, see the file here: http://cjoint.com/?krspgm0rSz

Hope this helps.


Barry
 
I honestly appreciate your efforts and will try to apply them and let you
folks know how it came out.
I should say if I could post this file to some site for those of you to
view, I think you would be able to see my situation clearer.

So with that said, see the file here: http://cjoint.com/?krspgm0rSz

Hope this helps.


Barry
Click to expand...

Given the way you have set up your worksheet, I would just enter a stay that
overlaps your Rate Change on multiple lines, making sure the Stop Date is in
the same "range" as the Start date.


So you might have, for example:

Start Date Stop Date Days of Stay Rate
5/1/2007 5/25/2007 =b20-a20+1
5/26/2007 5/30/2007 =b21-a21+1

And then Rate would be

=IF(AND(A20>=DATE(YEAR(a20),1,1),a20<=DATE(YEAR(A20),5,25)),Rate1,Rate2)

and the Total would be Rate x Days of Stay.

--ron
 
Ron you're absolutely right, and I thought of that. I just didn't know how
to fix it. I was going to decide to use the begining/start date for the
calculation. If there is another way to include the calculation you mention
I'll be happy to post the workbook somewhere for you folks to view and
modify, as I'm not interested in selling it or anything.

Just name the place

Barry
Click to expand...

Barry,

Assuming you do implement the date range you can use the following
formula, assuming your data is configured as follows:

B1=low rate , D1=high rate

A2=Start Date , B2=End Date , C2=Total Cost For Stay
The formula for C2 would be:
=IF(A2<=DATE(YEAR(A2),5,25),IF(B2<=DATE(YEAR(A2),5,25),(B2-A2+1)*$B$1,
(DATE(YEAR(A2),5,25)-A2+1)*$B$1+(B2-A2)*$D$1),(B2-A2+1)*$D$1)

You will need to also include criteria for a scenerio when a rental is
made during a change of a year but this should get you started.

Regards,

Dustin
 
Ron you're absolutely right, and I thought of that. I just didn't know how
to fix it. I was going to decide to use the begining/start date for the
calculation. If there is another way to include the calculation you mention
I'll be happy to post the workbook somewhere for you folks to view and
modify, as I'm not interested in selling it or anything.

Just name the place

Barry
Click to expand...

Barry,

Assuming you do implement the date range you can use the following
formula, assuming your data is configured as follows:

B1=low rate , D1=high rate

A2=Start Date , B2=End Date , C2=Total Cost For Stay
The formula for C2 would be:
=IF(A2<=DATE(YEAR(A2),5,25),IF(B2<=DATE(YEAR(A2),5,25),(B2-A2+1)*$B$1,
(DATE(YEAR(A2),5,25)-A2+1)*$B$1+(B2-A2)*$D$1),(B2-A2+1)*$D$1)

You will need to also include criteria for a scenerio when a rental is
made during a change of a year but this should get you started.
 
So with that said, see the file here: http://cjoint.com/?krspgm0rSz

Just in case you missed it, you have a spelling mistake on the form. You
have this....

Citry,State Zip:

where City is misspelled, there is no space after the comma and there is no
comma after the word "State".

Rick
 
Back
Top