FORMULAS USING DATES?

loki520 · Apr 21st, 2002, 07:18 AM

I've got a spreadsheet that lists, among other things, beginning balances, amounts to be paid, and ending balances (Beginning - paid = ending). Is there any way to make this formula occur ONLY, and automatically, on the 1st of every month without any user intervention? If I was to leave this file closed for 2 months and then open it, what would it take for it to realize 2 months have passed and process the amount paid twice?

Scott

smozgur · Apr 21st, 2002, 09:15 AM

Hi Scott,

If we will just use formulas in excel sheet to do this then you need a starting date/month as reference point. Say your A1 cell has this value as 01.01.2002

Now in A2 you can find the diffence of months beginning month and now with this easy formula:

=MONTH(NOW())-MONTH(A1)+1

It says (if we are in April now) 4 months and say you are increasing values by 1.15 multiplier for each month (means 15%). Now you can use B1 cell to calculate multiplier like below:

=1.15^A2

Easy? And you should care about year differences, please let me know to explain it also if you like this kind of solution.

Regards

Paul B · Apr 21st, 2002, 09:25 AM

Could you use something like this =IF(TODAY()>=A2,B2-C2,"") this will subtract C2 from B2 if the date in A2 is grater than or equal to today’s date, if not it will return “”, Blank.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 21st, 2002, 07:18 AM	#1
loki520 New Member Join Date: Apr 2002 Location: U.S. Navy Posts: 5	I've got a spreadsheet that lists, among other things, beginning balances, amounts to be paid, and ending balances (Beginning - paid = ending). Is there any way to make this formula occur ONLY, and automatically, on the 1st of every month without any user intervention? If I was to leave this file closed for 2 months and then open it, what would it take for it to realize 2 months have passed and process the amount paid twice? Scott

Apr 21st, 2002, 09:15 AM	#2
smozgur BatCoder Join Date: Feb 2002 Location: Turkey Posts: 764	Hi Scott, If we will just use formulas in excel sheet to do this then you need a starting date/month as reference point. Say your A1 cell has this value as 01.01.2002 Now in A2 you can find the diffence of months beginning month and now with this easy formula: =MONTH(NOW())-MONTH(A1)+1 It says (if we are in April now) 4 months and say you are increasing values by 1.15 multiplier for each month (means 15%). Now you can use B1 cell to calculate multiplier like below: =1.15^A2 Easy? And you should care about year differences, please let me know to explain it also if you like this kind of solution. Regards

Apr 21st, 2002, 09:25 AM	#3
Paul B Board Regular Join Date: Feb 2002 Location: Georgia USA Posts: 544	Could you use something like this =IF(TODAY()>=A2,B2-C2,"") this will subtract C2 from B2 if the date in A2 is grater than or equal to today’s date, if not it will return “”, Blank.