Change cell value according to datetime

[techissue2008]

Hi

I have an excel worksheet to display all debtor amount and create date.

When the create date compare with system date which is over 90 days, the debtor amount will be deducted and bad debt amount increased.

How should I use VBA to work out this? The debtor and bad debt are in 2 worksheets.

Thanks

 

[venkat1926]

can be done. But I would not like to guess your sheets configuration. Any how in vba
"date" means today's date.
the oode can be something like this
if date-(create date range)>90 then
(do something)
end if

 

[techissue2008]

How can I change cell values in different ws after the number of day condition meet?

 

[Sal Paradise]

I think you're going to run into problems. If you run the macro every day, then if something is over 90 days overdue it will increase your amount of bad debt every time you run the macro. I don't think you want this. You'd be much better off having a formula to calculate bad debt from several sheets that updates on the basis of how many of the debts are over 90 days.

 

[techissue2008]

Dear Sir

However, the debtor account is updated daily, if debtor settled the balance, the due amount will be removed from debtor account. So that it should be ok to run the macro everyday.

What do you suggest?

 

[Sal Paradise]

When you update with macros, you don't have a record of what calculations have been done. That means that you need to be very careful about not making the same change multiple times, because you won't catch it. Formulas are self-correcting in that they reflect the data in the workbook. I suggest doing your calculations with formulas and helper columns if needed. Without more information, I can't tell any better.

 

[techissue2008]

The debtor ws records

date - cell C3
due amount - cell C4
bad debt - cell C10

VBA if condition

if date-(cell C3)>90 then

remove cell C4
add to cell C10

end if

How should I write that VBA? does date a variable used in VBA to represent system date?

 

[Sal Paradise]

That's for a single record. How many do you have? In what range? Will it always be okay to delete them and add to bad debt after C10, eliminating any record they existed?

 

[techissue2008]

There are 20 records from C3 to C23.

Yes, it is ok to delete the date and amount which have expired.


<!-- / message --><!-- sig -->

 

[Sal Paradise]

That makes no sense.

If the dates are in C3, values in C4, and bad debt in C10, then how can you have 20 records from C3 to C23? They overlap.

Try posting a shot of your worksheet with one of the HTML makers available (with names and values changed to protect confidentiality).