请问excel的timesheet的一个formula问题

luoluoyehua. · 发表于 2014-8-25 09:00:37

请问各位比较懂formular的同学们，我想写个formular来使表简单，可实在不知怎么用google去描述。。是这样举个小例子说明：

      Date from                                        days    Mon Tues  Wed       Thur       Frid       sat          sun
david-8 hrs/day       18.08.14-24.08.14       5          8             8       8          8          8
jason-9 hrs/day       18.08.14-24.08.14       6          9             9       9          9          9          9
patrick-10 hrs/day       18.08.14-24.08.14       5.5          10    10       10       10          10          5
一堆人，每周工作几天不同，但是每个人的每天工作hrs是定的，有8，9，10小时不等的。然后原本的report就是mon－sun每天都是满的。要根据前面那列工作的days来manually改写到底上了几天。我想在mon－sun这几个栏写个什么公式，
使得前面column if＝5，then mon－fri就5天，if=6.5，then mon－sat，外加sun 0.5天。
每个人的daily hours在别的mastersheet有。请问这个有解决办法没？而不是每次我都要用days那栏sort出几天来删减。。。
谢谢大家！！

jy00064164. · 发表于 2014-8-25 10:25:22

sorry i got no Chinese input and no excel with me at the moment, here is my thought:
add an extra row 'threshold' above your first row, input integer threshold 0-6 for Mon-Sun
for argument sake let's say 'threshold' row is in 'row 2', your 'days' column is in 'column D', your first person's Monday cell is 'E4'
In E4 input formula =if($D4-E$2>1,$D4,if($D4-E$2>0,($D4-E$2)*$D4,0))
drag it across and down
Basically the 'days' is above the threshold, it covered that day/part of the day; if below threshold, it hasn't covered yet therefore it's 0 hrs.
However your whole idea of using 'days' column as a control is rather restricted as it assumed the data is always continuous. If a person was sick on Wednesday, or worked half day on both saturday and sunday, the formula won't pick it up, though it met your requirement.

sidneygan. · 发表于 2014-8-25 11:21:39

jy00064164 发表于 2014-8-21 03:12

sorry i got no Chinese input and no excel with me at the moment, here is my thought:
add an extra r ...
基本ok，但是在 formula =if($D4-E$2>1,$D4,if($D4-E$2>0,($D4-E$2)*$D4,0))，第一个if判定成功后应该是hours，而不是$d4,同理第二个if判定成功以后也应该是（$D4-e$20*hours。

wcsydney2000. · 发表于 2014-8-25 11:59:20

looks like the DAYS=SUM OF TOTAL WEEKLY HOURS/HOURS PER DAY
FOR EXAMPLE: 5=40/8
6=54/9
5.5=55/10

sidneygan. · 发表于 2014-8-25 12:25:48

思路跟2楼相似，
A 员工名字
B Daily Hours
C Days
D-J Monday to Sunday （第一排 D1 to J2 对应放入0-6的数字）
公式第一步放在D3，=IF($C3-D$1>1,$B3,IF($C3-D$1>0,($C3-D$1)*$B3,""))
然后拖拽到相应的格子，

Aries2012. · 发表于 2014-8-25 12:33:52

如果days至少是5
D2=IFERROR(IF(COLUMN()-3>$C2,IF(($C2>5)*($C2>COLUMN()-4),$C2-IF(COLUMN()-3>6,6,5),""),1)*MID($A2,FIND("-",$A2)+1,2),"")
右下拉

niuren. · 发表于 2014-8-25 14:02:51

把daily hour算作一列，比如L，结合2楼的，把D4换成L4
=if($D4-E$2>1,$L4,if($D4-E$2>0,($D4-E$2)*$L4,0))

samolano. · 发表于 2014-8-25 14:46:19

jy00064164 发表于 2014-8-21 03:12

sorry i got no Chinese input and no excel with me at the moment, here is my thought:
add an extra r ...
不好意思，($D4-E$2)*$D4，是什么意思呢？