[budemeadows]

Hi

New to VB and tearing my hair out over this one.

I'm trying to pull together, what should be, a simple booking system on Excel.
I have a user form which asks for arrival date (into a text box) and a number of nights it tne takes away all the data entered drops it into a spreadsheet and then works out the departure date and displays that in anothe text box on the form. The total bill is also displayed to allow for error checking before the booking is confirmed.

departure date should be (I thought) simply arrivaldate + nights

If the arrival date is entered as 1/3/09 (d/m/yy) and nights = 0 then departure date displays as 1 March 09 - fine

enter arrival 1/3/09 and days = 3 then departure comes up as 1 June 09
this happens even if date is entered as 1 mar 09

if the date is entered as 13/3/09 and nights = 3 then the departure date comes up as 16 March 09

The dates which get entered on the spread sheet are as I want them to be for both dates

- the upshot is that if the entered date could be mm/dd/yy then it is treated by the form as such and the number of nights is added to the months, if the first number in the date is > 12 (can't be a month) then the form takes it as dd/mm/yy and adds to the days. I don't see how this can happen as the spread sheet has the right dates both ways and that's just a number at the end of it.

Why doesn't the form display the right date?

I've tried this always - calculating the new date in VB, using DateAdd and taking the correct departure date from the spreadsheet - same effect in all cases

[digioz]

Here is a silly question. In your excel spreadsheet, is the cell formatted as a "Date" or "Text"?

Pete

[Bandula]

Originally Posted by digioz

Here is a silly question. In your excel spreadsheet, is the cell formatted as a "Date" or "Text"?

Pete

Yes, It's Possible. We can set the format of the cell.

[budemeadows]

Hi all, been on holiday, sorry for the delay in responding - the cell on the excel sheet is formatted as a date, but the same thing happens if it's formatted as a number - displaying the date as a number. I've tried using the AddDate function in VB and it still does the same thing if the date is 22/05/08 then adding 2 days gives 24/05/08 both on thre spreadsheet and displays 24/05/08 in the text box on my user form. start with 02/05/08 and the spreadsheet has 04/05/08 but the user form shows 02/07(July)/08.

[Yeroon]

Looks like you are using European format: dd/mm/yyyy

Check your windows Control Panel -> Regional and Language Options. See if they are setup to use this format.

And how did you use DateAdd? Like so?

Code:

DateAdd(interval, number, date)
So adding 3 days would be
DateAdd("d", 3, "02/05/08")
Outputs:
05/05/08

[budemeadows]

I've just tried inputting the date via a pop-up calender using calender control 8.0.

Same problem - 21 november displays as 21 November but 2 November displays as 11 February - this time the dates dropping into my spreadsheet are coming up wrong as well.

What's going on?

[budemeadows]

I'm using dateadd as specified - if the day is >12 then it works fine if Day <13 then it adds months.