Link to home
Start Free TrialLog in
Avatar of Andrew Angell
Andrew AngellFlag for United States of America

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!
Avatar of Member_2_6492660_1
Member_2_6492660_1
Flag of United States of America image

ASKER CERTIFIED SOLUTION
Avatar of redmondb
redmondb
Flag of Afghanistan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Andrew Angell

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...

=now()-C1

Open in new window


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.
Actually, I just added round() around that and that seems to give me what I want.
angelleye,

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.
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.
Thanks!
Thanks, angelleye.