Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 #### |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 #### |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reusing formula | Excel Discussion (Misc queries) | |||
formula to calculate time difference crossing midnight | Excel Worksheet Functions | |||
Match then lookup | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions |