formula for excel spreadsheet


P

Pam

I am trying to creat a formula in my accounting spreadsheet that will put the
total in the appropriate column by the date column plus 0-30 days or next
column plus 31-60 days. I tried the sumif and kept getting errors. Help.
 
Ad

Advertisements

P

Pam

column c is a date format, column d is the total column, each column after
that is 0-30, 31-60, 61-90, 91-119,120-150,150+ respectively. I would like
the total in d to automatically transfer to each of these columns as the
number of days increase from the original date. Is that possible?
 
P

Pam

I tried this formula and got the result for one column, however when I
changed the date past 30 days it did not change the results.
=IF(C6+(0-30),D6,0)
 
E

Eduardo

Hi,
in the column of 0-30 enter

=IF((TODAY()-$C$3)<=30,$D3,"")

31-60

=IF(AND((TODAY()-$C$3)>30,(TODAY()-$C$3)<=60),$D3,"")

61-90

=IF(AND((TODAY()-$C$3)>60,(TODAY()-$C$3)<=90),$D3,"")

91-119

=IF(AND((TODAY()-$C$3)>90,(TODAY()-$C$3)<=119),$D3,"")

120-150

=IF(AND((TODAY()-$C$3)>119,(TODAY()-$C$3)<=150),$D3,"")

+150

=IF(AND((TODAY()-$C$3)>119,(TODAY()-$C$3)<=150),$D3,"")

then copy formulas down
 
E

Eduardo

opps I found a mistake use this ones in each column and then copy
=IF((TODAY()-$C3)<=30,$D3,"")
=IF(AND((TODAY()-$C3)>30,(TODAY()-$C3)<=60),$D3,"")
=IF(AND((TODAY()-$C3)>60,(TODAY()-$C3)<=90),$D3,"")
=IF(AND((TODAY()-$C3)>90,(TODAY()-$C3)<=119),$D3,"")
=IF(AND((TODAY()-$C3)>119,(TODAY()-$C3)<=150),$D3,"")
=IF((TODAY()-$C$3)>150,$D3,"")
 
Ad

Advertisements

E

Eduardo

in last formula use
=IF((TODAY()-$C3)>150,$D3,"")

the problem was that I enter C between $ so the calculation will always look
to that cell
 
P

Pam

Thank you Eduardo, I was out of the office for a couple of days. I will try
the formulas and let you know.
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel Need aid in Excel Formulas 3
Excel Account Receivable Aging Report 1
Excel Colour code dates 1
Delete a formula but leave the data. 2
formula for spreadsheet 2
Excel Date Setting 1
two variable dates created from a single date 3
SUMIF Help 1

Top