need some help

bam12

Well-known Member
Joined
Dec 6, 2004
Messages
983
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

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

Well-known Member
Joined
Dec 6, 2004
Messages
983
ok thanks

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

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
=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

Well-known Member
Joined
Dec 6, 2004
Messages
983

ADVERTISEMENT

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

Well-known Member
Joined
Dec 6, 2004
Messages
983
help

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

bam12

Well-known Member
Joined
Dec 6, 2004
Messages
983

ADVERTISEMENT

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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
I'm not quite sure I understand

What range does Julian represent?

What's in column AJ?

Where's the hire date?
 

bam12

Well-known Member
Joined
Dec 6, 2004
Messages
983
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,685
Messages
5,637,803
Members
416,983
Latest member
LessThanAverageUser

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top