Dates in VBA

Resolved · Low Priority · Version 2007

Fri 18 Sep 2009, 16:59 Delegate Khurram said...

Khurram has attended:
Excel VBA Intro Intermediate course

Dates in VBA

If i wanted to insert a date, which would change on a daily bassis excluding bank holidays what code could i use?

thanks

Khurram

Tue 22 Sep 2009, 09:47 Trainer Stephen said...

RE: dates in VBA

Hi Khurram

Thank you for your question

This would be a substantial undertaking. You would need code that would compare the current date with a list of bank holiday dates. These dates would be contained in a range within a spreadsheet. You would need to build a loop that goes through the range a cell at a time and sees if any of them match todays date. If there was no match you would need to enter todays date. If however there was a match, you would need to increase today's date by one and then run this through the list of bank holidays. Keep increasing by 1 until you find a date not in the list, and insert this date.

Regards

Stephen

Mon 28 Sep 2009: Automatically marked as resolved.

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Excel tip:

Creating a range of monthly payments as text

You could use a formula to create a range of payment ie. payment amount for x% to y% rate with fixed terms and principle.

The text that would be "between Xamount and Yamount".

Here is how to do it.

1 Use the PMT function to get your monthly payments figure or whatever frequency of payments that you choose he start range.

See PMT under Excel Help

2. Nest these in the ROUND function to round decimals see ROUND under Excel Help

3. Concatenate this using "&" and concatenate " to " and concatenate "Between ".

4. Concatenate the above to PMT function for the end range

ie.

="Between "&ROUND((PMT1),decimal places)&" and "&ROUND((PMT2),decimal places)