Heres a Challenge for anyone!

[ram477]

Hi guys. ive got a complicated one (i think)

if i have a hotel and the room rate is different for each calendar date bracket eg:

01 august - 15 august = £15
16 august - 30 august = £25

Is there a way i can get excel to calulate the price for the customers stay. for example if they were staying 10 August until 20 August.

your guidance and suggestions are very much welcome on this one.

thanks in advance

Ram

 

[MickG]

Hi, Try this:- The code shows the principle , you will have to add details to the "Select Case" Dates/Costs.

[COLOR=navy]Sub[/COLOR] MG19Aug45
[COLOR=navy]Dim[/COLOR] Dts [COLOR=navy]As[/COLOR] [COLOR=navy]String,[/COLOR] Dt1 [COLOR=navy]As[/COLOR] Date, Dt2 [COLOR=navy]As[/COLOR] Date, Dt [COLOR=navy]As[/COLOR] Date, Cst [COLOR=navy]As[/COLOR] Double
[COLOR=navy]Dim[/COLOR] Tcs [COLOR=navy]As[/COLOR] Double
[COLOR=navy]On[/COLOR] [COLOR=navy]Error[/COLOR] [COLOR=navy]Resume[/COLOR] [COLOR=navy]Next[/COLOR]
 Dts = Application.InputBox(prompt:="Please Insert Inclusive dates :-" _
 & Chr(10) & "Example:- 10/8/2009-20/8/2009 ", _
        Title:="Insert Brackets", Type:=2)
[COLOR=navy]If[/COLOR] Dts = "False" [COLOR=navy]Then[/COLOR] [COLOR=navy]Exit[/COLOR] [COLOR=navy]Sub[/COLOR]
    Dt1 = Split(Dts, "-")(0)
        Dt2 = Split(Dts, "-")(1)
[COLOR=navy]For[/COLOR] Dt = Dt1 To Dt2
    [COLOR=navy]Select[/COLOR] [COLOR=navy]Case[/COLOR] Dt
        [COLOR=navy]Case[/COLOR] "1/8/2009" To "15/8/2009": Cst = 15
        [COLOR=navy]Case[/COLOR] "16/8/2009" To "30/8/2009": Cst = 25
        [COLOR=navy]End[/COLOR] Select
            Tcs = Tcs + Cst
[COLOR=navy]Next[/COLOR] Dt
MsgBox "Cost for inclusive dates :- " & Chr(10) & Dts & " = £" & Tcs
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[barry houdini]

I responded with a formula approach to duplicate post here