need some help

[bam12]

I need some help in column B i put how many hours a person uses for PTO (Paid Time Off)
column A contains the date

this is the formula i have in column H

=SUMPRODUCT($B$7:$B$91,--(JULIAN>0),--(JULIAN<$AJ7+1))

Now i have to break it down by vacation or sick time
So in colum D I am going to put either a V or S so if it is V it will use the above formula and in a new column i am going to make for S

I guesss what i need is an IF Statement to add to the above formula and the new column for Sick time

Hope fully this example will show what i need

A B D H K
8/5 8.00 V 8.00 0.00
8/25 8.00 S 0.00 8.00
9/1 8.00 V 16.00 0.00
hope you understand what I am trying to do

Thanks in advance for any help

 

[barry houdini]

If you want to sum the same hours but only where column D is "S"

=SUMPRODUCT($B$7:$B$91,--(JULIAN>0),--(JULIAN<$AJ7+1),--($D$7:$D$91="S"))

similar for "V"

 

[bam12]

ok thanks

will give it a try when i have time, thanks for the help will let you know how it works

 

[texasalynn]

=SUMPRODUCT($B$7:$B$91,--(JULIAN>0),--(JULIAN<$AJ7+1))

try this
=SUMPRODUCT((($A$7:$A$91>0)*--($A$7:$A$91<$AJ7+1)*($D$7:$D$91="V")*($B$7:$B$91)))

 

[bam12]

working but have problem

It seems to be working but ran into a problem

I use to go from Jan 1 to Dec 31 but now have to go from person hire date, so by using the julian table i have set up it is putting time in wrong area

What i mean is if use vacation on 1/4/07 it is adding it to 1/4/06 line

I have a line for each payweek showing what pay they used vacation, so that is what i am using when i say the 1/4/06 line

 

[bam12]

help

anyone know what i can do to fix this last problem?

 

[bam12]

julian formula

this is the formula i have for the julian date

=$A7-DATE(YEAR($A7),1,0)

but now need to go from one year to another

 

[barry houdini]

I'm not quite sure I understand

What range does Julian represent?

What's in column AJ?

Where's the hire date?

 

[bam12]

julian

julian is a table i set up (name) V7to V90
it takes the date in column A and changes it to julian

column AJ is julain date for payweek end, sorry about that

hire day will be in F7

 

[barry houdini]

Sorry, you'll have to spell it out for me. I understand that you want to total the hours in column B where column D is "V" but for what range of dates?

I don't see where the hire date comes in, surely all vacation time will, by definition, be after the hire date?