Need Date Formula Help
 

This formula is supposed to subtract one day every day. It works perfectly to
30 days, and then on the 31st day, it resets to zero. It needs to go
indefinitely, and I'm at a complete loss. Any suggestions?

=R19-DATE(YEAR(R19),MONTH(R19)-IF(DAY(R19)<DAY(L19),1,0),DAY(L19))&"days"

Excel 2003

Need Date Formula Help
 

What is in R19, what in L19 and what exactly do you want tp happen?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"vegasrenie" wrote in message
...
This formula is supposed to subtract one day every day. It works perfectly
to
30 days, and then on the 31st day, it resets to zero. It needs to go
indefinitely, and I'm at a complete loss. Any suggestions?

=R19-DATE(YEAR(R19),MONTH(R19)-IF(DAY(R19)<DAY(L19),1,0),DAY(L19))&"days"

Excel 2003

Need Date Formula Help
 

You'll need to tell us what dates you have in R19 and L19 and tell us why
you're qualifying the DAY in this:

IF(DAY(R19)<DAY(L19),1,0),DAY(L19)

--
Biff
Microsoft Excel MVP


"vegasrenie" wrote in message
...
This formula is supposed to subtract one day every day. It works perfectly
to
30 days, and then on the 31st day, it resets to zero. It needs to go
indefinitely, and I'm at a complete loss. Any suggestions?

=R19-DATE(YEAR(R19),MONTH(R19)-IF(DAY(R19)<DAY(L19),1,0),DAY(L19))&"days"

Excel 2003

Need Date Formula Help
 

It isn't clear what you are trying to do, but don't forget that neither
DAY(R19) nor DAY(L19) will ever go beyond 31, because they are days in a
month.
Why not tell us what your L19 and R19 are, and what you are trying to
achieve with your formula?
As far as I can see the DATE function is giving you a date where the DAY
portion is the same day of the month as the date in L19, and the month is
the month before R19 if R19 is an earlier day of the month than L19, or
otherwise the same month as R19.
Hence as you increase L19 from the beginning of the month you are
progressively bringing the DATE function closer to R19 until when the day of
L19 equals the day of R19 you get R19 from your date function (and 0 days
from your complete formula), then when L19 goes one day beyond R19's day of
the month, the DATE function then goes back to the previous month, and
therefore the result of the formula is one less than the number of days in
the month before R19. The maximum will therefore be 30 if there were 31
days in that preceding month, or 29 if there were 30 days in that preceding
month, or 27 (or 28) if the preceding month was February (i.e. if R19 was in
March).

Does that explain it for you?
--
David Biddulph

"vegasrenie" wrote in message
...
This formula is supposed to subtract one day every day. It works perfectly
to
30 days, and then on the 31st day, it resets to zero. It needs to go
indefinitely, and I'm at a complete loss. Any suggestions?

=R19-DATE(YEAR(R19),MONTH(R19)-IF(DAY(R19)<DAY(L19),1,0),DAY(L19))&"days"

Excel 2003