Andrew Angell

asked on

# How to count "days back" in Excel..??

I've got a simple spreadsheet where I'm trying to calculate money owed on a lease including late fees. The late fee is $25/day. I have a cell for "days late" but right now I have to keep updating it manually. I'd like to automatically calculate the current number of days late by counting back from the current day to the original due date.

Can't seem to find the formula to do this for me. Any info on this would be great. Thanks!

Can't seem to find the formula to do this for me. Any info on this would be great. Thanks!

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

That doesn't look like it's for dates..??

As it stands now, I have the due date stored in column C, so this seems to work for the most part...

The only issue I'm having is that it's actually using half days with that, because of the use of now(). So, for example, since it's 12:00 p.m. right now where I'm at it's calculating the days back as 103.50.

I can work with that, but it seems like there would be something that wouldn't use fractions of days like that..?? I tried today()-C1, but that returns a new date value instead of a number.

As it stands now, I have the due date stored in column C, so this seems to work for the most part...

`=now()-C1`

The only issue I'm having is that it's actually using half days with that, because of the use of now(). So, for example, since it's 12:00 p.m. right now where I'm at it's calculating the days back as 103.50.

I can work with that, but it seems like there would be something that wouldn't use fractions of days like that..?? I tried today()-C1, but that returns a new date value instead of a number.

ASKER

Actually, I just added round() around that and that seems to give me what I want.

angelleye,

Yes, that's why I stuck in "INT".

So, if you want 103 as the answer then my original formula is correct, but if you want 104 then it becomes...

=INT((NOW()-C1))+1

Regards,

Brian.

*The only issue I'm having is that it's actually using half days with that*Yes, that's why I stuck in "INT".

So, if you want 103 as the answer then my original formula is correct, but if you want 104 then it becomes...

=INT((NOW()-C1))+1

Regards,

Brian.

ASKER

redmondb, sorry, I didn't even see your response. I was responding to the link that trgrassijr55 gave me when I said it didn't look like it was working with dates.

angelleye,

No problem - I guessed as much!

BTW, careful with ROUND as you may get different results depending on whether it's before or after midday!

Regards,

Brian.

No problem - I guessed as much!

BTW, careful with ROUND as you may get different results depending on whether it's before or after midday!

Regards,

Brian.

ASKER

Thanks!

Thanks, angelleye.

http://en.allexperts.com/q/Excel-1059/2009/2/Excel-formulas-counting-backwards.htm

Hope it helps