Calculating Hours worked by day

[rapscalli]

Hi all,

i have a task list with

• hours
• start date
• finish date

In columns a, b and c

In row 1, from D1:AK1 i have dates as table headers.

Below these dates i would like to calculate the hours scheduled for the task described on that row.

Eg a task starting may 1st, ending may 3rd, 3 hrs work.

I would like to see 1 hr under may 1st, may 2nd and may 3rd.

I had this formula calculated myself some time ago but have mislaid it, and it has been some time since i've used excel in anger!

[dilipandey]

Hi rapscalli,

Welcome to the forum.

Use the below formula in cell D2 and drag it to right and down....

=IF(AND(D$1>=$B2,D$1<=$C2),$A2/(($C2-$B2)+1),2)

Regards,
DILIPandey

<click on below 'star' if this helps>

[rapscalli]

Hi rapscalli,

Welcome to the forum.

Use the below formula in cell D2 and drag it to right and down....

=IF(AND(D$1>=$B2,D$1<=$C2),$A2/(($C2-$B2)+1),2)

Regards,
DILIPandey

<click on below 'star' if this helps>

hi DILIPandey,

many thanks, i've got it working with your help. I also had to add in
WEEKDAY(F$1,2)<6
to avoid weekends
and
(ISERROR(VLOOKUP(F$1,'logic-Holidays'!$A:$A,1,0))
to avoid holidays.

It is all working correctly now. If only i could get the data in the correct format from ms project!

[dilipandey]

Good logics....

cheers

Regards,
DILIPandey

<click on below 'star' if this helps>