YTD formula

**xbennykinsx** · 10-25-2010, 02:06 PM

Hello all, first time posting. trying to figure out this YTD formula for work, could anyone help me.

Thanks

**NBVC** · 10-25-2010, 02:19 PM

It is not clear to me where the problem is? Which sheet and what is the logic you are looking for?

**xbennykinsx** · 10-25-2010, 02:26 PM

on the first page in L7 we need the YTD to calculate.

**NBVC** · 10-25-2010, 02:32 PM

Based on a monthly pay...

try:

=O5*MONTH(C7)

**xbennykinsx** · 10-25-2010, 02:48 PM

Sorry in i7 i need to figure out YTD based off of Jan 1 and the date we put in C7

**NBVC** · 10-25-2010, 02:57 PM

Did you test my formula?

Is it what you need?

**xbennykinsx** · 10-25-2010, 03:06 PM

sorry, i was under the assumption of something else by an employee, what we are looking for is in L6 if it is the 10th month we need it to say 10 but we have to have an exact YTD so if it is the 27th day we need that cell to be 10.27 to give us the correct output of the YTD. if that makes sense.

**NBVC** · 10-25-2010, 03:36 PM

To get the 10 in L7, simply: =MONTH(C7)

To get the YTD Gross, if you use similar logic to your calc of monthly rate in O5, then try:

=I5*L5*52/365*(C7-DATE(YEAR(TODAY()),1,1))

**xbennykinsx** · 10-25-2010, 03:46 PM

didnt work.. :/ hmmmm

**NBVC** · 10-25-2010, 03:47 PM

What does that mean?

Error? Wrong answer? Please elaborate and also provide expected result for sample data.

**xbennykinsx** · 10-25-2010, 07:04 PM

Sorry, wrong answer.

If C7 is 10/20/10 and I7 is 38,999.97 O7 should be 4,037.26 L7 should be 9.66

**NBVC** · 10-26-2010, 09:09 AM

I think your sample date should have been 9/20/10 not 10/20/10 for those results, yes?

If so, then in L7, try:

=MONTH(C7)+DAY(C7)/DAY(EOMONTH(C7,0))