mod formula used with midnight
 

We have a few employees who work an overnight shift. They work (4) 10 hours
shifts. Their shift begins at 630p and ends at 530am. Here is the formula I
am using to calculate the work week for our other employees who work duing
the day time hours, but I need help in adjusting it for our overnight shift.

=SUM((MOD(COLUMN(C4:P4),2)=0)*C4:P4)-SUM(((MOD(COLUMN(C4:P4),2)=1)*C4:P4))-SUM(--(IF(MOD(COLUMN(D4:Q4),2)=0,D4:Q4)-IF(MOD(COLUMN(C4:P4),2)=1,C4:P4)0.25))/48

(shift+ctrl+enter)

My current result is ####

mod formula used with midnight
 

S in AZ,

Assuming that you want the MOD() function on the first part of the formula

=SUM(MOD((MOD(COLUMN(C4:P4),2)=0)*C4:P4-((MOD(COLUMN(C4:P4),2)=1)*C4:P4),1))-SUM(--(IF(MOD(COLUMN(D4:Q4),2)=0,D4:Q4)-IF(MOD(COLUMN(C4:P4),2)=1,C4:P4)0.25))/48

Seems to return the correct result. Of course not knowing what was in Row 4
I just left them all blank.

Like your original formula array entered.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"S in AZ" wrote in message
...
We have a few employees who work an overnight shift. They work (4) 10
hours
shifts. Their shift begins at 630p and ends at 530am. Here is the
formula I
am using to calculate the work week for our other employees who work duing
the day time hours, but I need help in adjusting it for our overnight
shift.

=SUM((MOD(COLUMN(C4:P4),2)=0)*C4:P4)-SUM(((MOD(COLUMN(C4:P4),2)=1)*C4:P4))-SUM(--(IF(MOD(COLUMN(D4:Q4),2)=0,D4:Q4)-IF(MOD(COLUMN(C4:P4),2)=1,C4:P4)0.25))/48

(shift+ctrl+enter)

My current result is ####