dates in year

[MLD]

I have a formula set to count the number of taxable daysin a year based on
the closing date of the transaction. The problem is, I have used the
=DATE360 function which is 5 (or 6) days off and is causing a problem.

My current function reads:
=IF(C2=0,0,(DAYS360(D2,E2))) C2=Jan 1 of current year; D2 = Jan 1 minus 1
; E2=.ref to closing date cell

The number of days is then mul;tiplied by the daily tax raite of the
property which is figured by annual tax/365.
See the problem? Has anyone run into this issue? Do you have a workaround
formula?


Thanks so much!
-Monica

 

[Ron Rosenfeld]

I have a formula set to count the number of taxable daysin a year based on
the closing date of the transaction. The problem is, I have used the
=DATE360 function which is 5 (or 6) days off and is causing a problem.

My current function reads:
=IF(C2=0,0,(DAYS360(D2,E2))) C2=Jan 1 of current year; D2 = Jan 1 minus 1
; E2=.ref to closing date cell

The number of days is then mul;tiplied by the daily tax raite of the
property which is figured by annual tax/365.
See the problem? Has anyone run into this issue? Do you have a workaround
formula?


Thanks so much!
-Monica

If every day in the year is a "taxable day", then, to count the number of days,
inclusive, from the beginning of the year to the closing date in E2, you can
use:

=E2-DATE(YEAR(TODAY()),1,0)

Format the result as General (or as Number with 0 decimal places)


--ron

 

[MLD]

Thanks!! Works like a charm (even thogh I can't quite get the logic of it.)

 

[Ron Rosenfeld]

Thanks!! Works like a charm (even thogh I can't quite get the logic of it.)

The logic of it is that Excel stores dates as serial numbers with day 1 being
referenced to 1/1/1900 or 1904 depending on the date system in use on your
machine.

So to get the number of days between dates, you can merely subtract one from
the other.

The portion of the formula "DATE(YEAR(TODAY()),1,0)" generates the date as
follows: DATE(year, month, day)

YEAR(TODAY()) is this year.
1 -- January
0 -- the 0'th day of january becomes the last day of the previous month --
necessary since you want an inclusive count.

Glad it works for you.

Thanks for the feedback.
--ron