# need some help

#### bam12

##### Well-known Member
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

### Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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"

ok thanks

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

=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)))

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

help

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

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

I'm not quite sure I understand

What range does Julian represent?

What's in column AJ?

Where's the hire date?

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

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?

Replies
8
Views
218
Replies
0
Views
171
Replies
2
Views
132
Replies
1
Views
361
Replies
3
Views
154

1,207,391
Messages
6,078,209
Members
446,321
Latest member
thecachingyeti

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back