excell comparisons

[Guest_imported]

Hi,
I am new to excell and am trying to do a spreadsheet that has some columns that compare date ranges. I need to compare the due date to todays date and separate the amount due(from another column) based on whether the dates are in the range of 0-30days late, 31 -60 days late, 61 to 90 days late, 91 to 180 days late, and lastly dates > 180 days from due date. I can get those > 180 with no problems but the others ranges I havent figured out. Thanks for any help...

 

[SimonDavis]

I don't know how much you've already worked out, so I'll go from the beginning.

If you have a column of due dates, (for example column C, starting row 1) you can put in column D the following:

=today()-c1

You can then copy that formula down the column (Click the cell with the formula,click the small square on the corner of the cell, hold it and drag down. It will autofill).

This will give you the number of days the payment is late in the second column.

You can then display that in several ways. I would suggest something like the following.

In column E you would put the data for 0-30 days, column F 30-60 etc. You can use an 'if' function to do this.

Again assuming, the amount is in column A, you would put the following formula in column D:

=if(c1>0,if(c1<31,+a1,0))

This will check if the days overdue number is greater than 0, and less than 31, if so it will place the value of a (the outstanding amount) in the cell.

You can repeat that in the next column, but change 0 to 30, and 31 to 61. That will give you the 30-60 day numbers, and so on accross the columns.

You can then use each column for totals etc.

If you have any problems, you can email a copy or a sample of your sheet to me and I'll get it started for you.

 

[Dreamboat]

BeginnerExcell, you might want to start by spelling Excel properly. No wonder it doesn't want to work for you!! Haha!

Simon is almost right. Unfortunately he has given you one of the very few functions that is available in Access, but not appropriate for use in Excel.

For Excel, use =NOW()-C1

Yes, the parentheses are weird as shown.
techsupportgirl@home.com
Brainbench MVP for Microsoft Word

http://www.brainbench.com

 

[SimonDavis]

Strange - it works on mine. I've got the UK version of Excel 97 - I know it can vary because I live in Brazil and the Portuguese version is wierd as hell.

I use the TODAY function because if you use NOW, it will give a different number dependng on whether it is before or after midday (it's more literal - it really means NOW).

Maybe try both and see which works best for you?

 

[Dreamboat]

I take back what I said!

=TODAY() gives you today's date
=NOW() gives you the current time (and date if you've got it formatted to show it)
techsupportgirl@home.com
Brainbench MVP for Microsoft Word

http://www.brainbench.com

 

[Guest_imported]

Thanks a heap,
thanks again for all the help...