{"text_format":"1","workbook":"xlsx","n_sheets":5,"sheets":["Question","Try","Bill","Mike","Sheet1"],"not_rendered":{"charts":0,"images":0,"macros":0,"external_links":0,"defined_names":0,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Question","index":0,"n_rows":17,"n_cols":8,"rows_kept":17,"cut":false,"hidden":false,"merged":[]}
["Employee","Monthly Pay","Start Date","End Date"]
["Joe",1500,"2013-05-14","2013-09-09"]
[null,null,null,null,null,"Intersection Formula!"]
[null,"Month","Start","End","# of Days in Month","Number of Days\nThat employee was\nHere this month","% of Month","Pay for Month"]
[null,"January",{"$f":"DATEVALUE(B5&\" 1, 2013\")","v":"2013-01-01"},{"$f":"EOMONTH(C5,0)","v":"2013-01-31"},{"$f":"D5-C5+1","v":31},null,null,{"$f":"+G5*$B$2","v":0}]
[null,"February",{"$f":"DATEVALUE(B6&\" 1, 2013\")","v":"2013-02-01"},{"$f":"EOMONTH(C6,0)","v":"2013-02-28"},{"$f":"D6-C6+1","v":28},null,null,{"$f":"+G6*$B$2","v":0}]
[null,"March","2013-03-01","2013-03-31",31,null,null,0]
[null,"April","2013-04-01","2013-04-30",30,null,null,0]
[null,"May","2013-05-01","2013-05-31",31,null,null,0]
[null,"June","2013-06-01","2013-06-30",30,null,null,0]
[null,"July","2013-07-01","2013-07-31",31,null,null,0]
[null,"August","2013-08-01","2013-08-31",31,null,null,0]
[null,"September","2013-09-01","2013-09-30",30,null,null,0]
[null,"October","2013-10-01","2013-10-31",31,null,null,0]
[null,"November","2013-11-01","2013-11-30",30,null,null,0]
[null,"December","2013-12-01","2013-12-31",31,null,null,0]
[null,"Total",null,null,null,null,null,{"$f":"SUM(H5:H16)","v":0}]

{"sheet":"Try","index":1,"n_rows":17,"n_cols":8,"rows_kept":17,"cut":false,"hidden":false,"merged":[]}
["Employee","Monthly Pay","Start Date","End Date"]
["Joe",1500,"2013-05-14","2013-09-09"]
[null,null,null,null,null,"Intersection Formula!"]
[null,"Month","Start","End","# of Days in Month","Number of Days\nThat employee was\nHere this month","% of Month","Pay for Month"]
[null,"January",{"$f":"DATEVALUE(B5&\" 1, 2013\")","v":"2013-01-01"},{"$f":"EOMONTH(C5,0)","v":"2013-01-31"},{"$f":"D5-C5+1","v":31},null,null,{"$f":"+G5*$B$2","v":0}]
[null,"February",{"$f":"DATEVALUE(B6&\" 1, 2013\")","v":"2013-02-01"},{"$f":"EOMONTH(C6,0)","v":"2013-02-28"},{"$f":"D6-C6+1","v":28},null,null,{"$f":"+G6*$B$2","v":0}]
[null,"March","2013-03-01","2013-03-31",31,null,null,0]
[null,"April","2013-04-01","2013-04-30",30,null,null,0]
[null,"May","2013-05-01","2013-05-31",31,null,null,0]
[null,"June","2013-06-01","2013-06-30",30,null,null,0]
[null,"July","2013-07-01","2013-07-31",31,null,null,0]
[null,"August","2013-08-01","2013-08-31",31,null,null,0]
[null,"September","2013-09-01","2013-09-30",30,null,null,0]
[null,"October","2013-10-01","2013-10-31",31,null,null,0]
[null,"November","2013-11-01","2013-11-30",30,null,null,0]
[null,"December","2013-12-01","2013-12-31",31,null,null,0]
[null,"Total",null,null,null,null,null,{"$f":"SUM(H5:H16)","v":0}]

{"sheet":"Bill","index":2,"n_rows":22,"n_cols":8,"rows_kept":22,"cut":false,"hidden":false,"merged":[]}
["Employee","Monthly Pay","Start Date","End Date"]
["Joe",1500,"2013-05-14","2013-09-09"]
[null,null,null,null,null,"Intersection Formula!"]
[null,"Month","Start","End","# of Days in Month","Number of Days\nThat employee was\nHere this month","% of Month","Pay for Month"]
[null,"January",{"$f":"DATEVALUE(B5&\" 1, 2013\")","v":"2013-01-01"},{"$f":"EOMONTH(C5,0)","v":"2013-01-31"},{"$f":"D5-C5+1","v":31},{"$f":"MAX(0,MIN(D5,$D$2)-MAX(C5,$C$2)+1)","v":0},{"$f":"F5/E5","v":0},{"$f":"+G5*$B$2","v":0}]
[null,"February",{"$f":"DATEVALUE(B6&\" 1, 2013\")","v":"2013-02-01"},{"$f":"EOMONTH(C6,0)","v":"2013-02-28"},{"$f":"D6-C6+1","v":28},0,0,{"$f":"+G6*$B$2","v":0}]
[null,"March","2013-03-01","2013-03-31",31,0,0,0]
[null,"April","2013-04-01","2013-04-30",30,0,0,0]
[null,"May","2013-05-01","2013-05-31",31,18,0.58064516129032262,870.9677419354839]
[null,"June","2013-06-01","2013-06-30",30,30,1,1500]
[null,"July","2013-07-01","2013-07-31",31,31,1,1500]
[null,"August","2013-08-01","2013-08-31",31,31,1,1500]
[null,"September","2013-09-01","2013-09-30",30,9,0.3,450]
[null,"October","2013-10-01","2013-10-31",31,0,0,0]
[null,"November","2013-11-01","2013-11-30",30,0,0,0]
[null,"December","2013-12-01","2013-12-31",31,0,0,0]
[null,"Total",null,null,null,null,"Method 1",{"$f":"SUM(H5:H16)","v":5820.9677419354839}]
[]
[null,null,null,null,null,null,"Method 2",{"$f":"B2*12","v":18000}]
[null,null,null,null,null,null,null,{"$f":"D2-C2+1","v":119}]
[null,null,null,null,null,null,null,{"$f":"H20/365","v":0.32602739726027397}]
[null,null,null,null,null,null,null,{"$f":"H21*H19","v":5868.4931506849316}]

{"sheet":"Mike","index":3,"n_rows":20,"n_cols":11,"rows_kept":20,"cut":false,"hidden":false,"merged":[]}
["Employee","Monthly Pay","Start Date","End Date","Pay","Same Month","2 Months","> 2 Months","John Allison","Bruce Porter","Bill Szysz"]
["Joe",1500,"2013-05-14","2013-09-09",{"$f":"(D2-C2+1)/365*(B2*12)","v":5868.4931506849316},null,null,null,{"$f":"(((EOMONTH(C2,0)-C2)+1)/DAY(EOMONTH(C2,0))+(MONTH(D2)-MONTH(C2)-1)+(DAY(D2)/DAY(EOMONTH(D2,0))))*B2","v":5820.9677419354839},{"$f":"SUM(DATEDIF(EOMONTH(C2,0),EOMONTH(D2,-1),\"m\"),(EOMONTH(C2,0)-C2+1)/DAY(EOMONTH(C2,0)),DAY(D2)/DAY(EOMONTH(D2,0)))*B2","v":5820.9677419354839},{"$f":"(IF(EOMONTH(C2,0)=EOMONTH(D2,0),-1,DATEDIF(EOMONTH(C2,0),EOMONTH(D2,-1)+1,\"m\"))+(EOMONTH(C2,0)-C2+1)/DAY(EOMONTH(C2,0))+DAY(D2)/DAY(EOMONTH(D2,0)))*B2","v":5820.9677419354839}]
["Joe",1500,"2013-05-14","2013-05-15",{"$f":"B3*CHOOSE(LOOKUP(MONTH(D3)-MONTH(C3)+1,{1,2,3}),(D3-C3+1)/DAY(EOMONTH(D3,0)),(EOMONTH(C3,0)-C3+1)/DAY(EOMONTH(C3,0))+(D3-EOMONTH(D3,-1))/DAY(EOMONTH(D3,0)),(EOMONTH(C3,0)-C3+1)/DAY(EOMONTH(C3,0))+(D3-EOMONTH(D3,-1))/DAY(EOMONTH(D3,0))+(MONTH(D3)-MONTH(C3)-1))","v":96.774193548387089},{"$f":"(D3-C3+1)/DAY(EOMONTH(D3,0))","v":6.4516129032258063E-2},{"$f":"(EOMONTH(C3,0)-C3+1)/DAY(EOMONTH(C3,0))+(D3-EOMONTH(D3,-1))/DAY(EOMONTH(D3,0))","v":1.064516129032258},{"$f":"(EOMONTH(C3,0)-C3+1)/DAY(EOMONTH(C3,0))+(D3-EOMONTH(D3,-1))/DAY(EOMONTH(D3,0))+(MONTH(D3)-MONTH(C3)-1)","v":6.4516129032258007E-2},{"$f":"(((EOMONTH(C3,0)-C3)+1)/DAY(EOMONTH(C3,0))+(MONTH(D3)-MONTH(C3)-1)+(DAY(D3)/DAY(EOMONTH(D3,0))))*B3","v":96.774193548387174},{"$f":"SUM(DATEDIF(EOMONTH(C3,0),EOMONTH(D3,-1),\"m\"),(EOMONTH(C3,0)-C3+1)/DAY(EOMONTH(C3,0)),DAY(D3)/DAY(EOMONTH(D3,0)))*B3","v":{"$error":"#NUM!"}},{"$f":"(IF(EOMONTH(C3,0)=EOMONTH(D3,0),-1,DATEDIF(EOMONTH(C3,0),EOMONTH(D3,-1)+1,\"m\"))+(EOMONTH(C3,0)-C3+1)/DAY(EOMONTH(C3,0))+DAY(D3)/DAY(EOMONTH(D3,0)))*B3","v":96.774193548387174}]
["Joe",1500,"2013-05-14","2013-06-09",{"$f":"B4*CHOOSE(LOOKUP(MONTH(D4)-MONTH(C4)+1,{1,2,3}),(D4-C4+1)/DAY(EOMONTH(D4,0)),(EOMONTH(C4,0)-C4+1)/DAY(EOMONTH(C4,0))+(D4-EOMONTH(D4,-1))/DAY(EOMONTH(D4,0)),(EOMONTH(C4,0)-C4+1)/DAY(EOMONTH(C4,0))+(D4-EOMONTH(D4,-1))/DAY(EOMONTH(D4,0))+(MONTH(D4)-MONTH(C4)-1))","v":1320.9677419354839},null,{"$f":"(EOMONTH(C4,0)-C4+1)/DAY(EOMONTH(C4,0))+(D4-EOMONTH(D4,-1))/DAY(EOMONTH(D4,0))","v":0.88064516129032255},0.88064516129032255,1320.9677419354839,1320.9677419354839,1320.9677419354839]
["Joe",1500,"2013-05-14","2014-09-09",{"$f":"B5*CHOOSE(LOOKUP(MONTH(D5)-MONTH(C5)+1,{1,2,3}),(D5-C5+1)/DAY(EOMONTH(D5,0)),(EOMONTH(C5,0)-C5+1)/DAY(EOMONTH(C5,0))+(D5-EOMONTH(D5,-1))/DAY(EOMONTH(D5,0)),(EOMONTH(C5,0)-C5+1)/DAY(EOMONTH(C5,0))+(D5-EOMONTH(D5,-1))/DAY(EOMONTH(D5,0))+(MONTH(D5)-MONTH(C5)-1))","v":5820.9677419354839},null,null,{"$f":"(EOMONTH(C5,0)-C5+1)/DAY(EOMONTH(C5,0))+(D5-EOMONTH(D5,-1))/DAY(EOMONTH(D5,0))+(MONTH(D5)-MONTH(C5)-1)","v":3.8806451612903228},5820.9677419354839,23820.967741935485,23820.967741935485]
[]
[null,"1) Re-write contact"]
[null,"2) Get barry houdini at the Mr Excel Message Board to create Magic Date Formulas"]
[null,"3) Make a formula that is too long"]
[]
[null,"Ctrl + Shift + ~ ( ` ) = General Number Format"]
[]
[]
[]
[]
[]
[]
[null,null,null,null,null,{"$f":"LOOKUP(MONTH(D3)-MONTH(C3)+1,{1,2,3})","v":1},{"$f":"IF(MONTH(D3)=MONTH(C3),(D3-C3+1)/DAY(EOMONTH(D3,0)))","v":6.4516129032258063E-2},{"$f":"(EOMONTH(C3,0)-C3+1)/DAY(EOMONTH(C3,0))+(D3-EOMONTH(D3,-1))/DAY(EOMONTH(D3,0))","v":1.064516129032258},{"$f":"(EOMONTH(C3,0)-C3+1)/DAY(EOMONTH(C3,0))+(D3-EOMONTH(D3,-1))/DAY(EOMONTH(D3,0))+(MONTH(D3)-MONTH(C3)-1)","v":6.4516129032258007E-2},null,{"$f":"2/31","v":6.4516129032258063E-2}]
[null,null,null,null,null,{"$f":"LOOKUP(MONTH(D4)-MONTH(C4)+1,{1,2,3})","v":2},null,{"$f":"(EOMONTH(C4,0)-C4+1)/DAY(EOMONTH(C4,0))+(D4-EOMONTH(D4,-1))/DAY(EOMONTH(D4,0))","v":0.88064516129032255},{"$f":"(EOMONTH(C4,0)-C4+1)/DAY(EOMONTH(C4,0))+(D4-EOMONTH(D4,-1))/DAY(EOMONTH(D4,0))+(MONTH(D4)-MONTH(C4)-1)","v":0.88064516129032255},null,{"$f":"18/31+9/30","v":0.88064516129032255}]
[null,null,null,null,null,{"$f":"LOOKUP(MONTH(D5)-MONTH(C5)+1,{1,2,3})","v":3},null,null,{"$f":"(EOMONTH(C5,0)-C5+1)/DAY(EOMONTH(C5,0))+(D5-EOMONTH(D5,-1))/DAY(EOMONTH(D5,0))+(MONTH(D5)-MONTH(C5)-1)","v":3.8806451612903228}]

{"sheet":"Sheet1","index":4,"n_rows":17,"n_cols":8,"rows_kept":17,"cut":false,"hidden":false,"merged":[]}
[]
[]
[]
[]
[]
[null,null,null,{"$f":"EOMONTH(ROW(INDIRECT(C5&\":\"&D5)),0)-EOMONTH(ROW(INDIRECT(C5&\":\"&D5)),-1)","v":{"$error":"#REF!"}}]
[]
[null,null,null,null,"Total months minus 1"]
[null,null,null,null,{"$f":"MONTH(Mike!D3)-MONTH(Mike!C3)","v":0},null,0,{"$f":"CHOOSE(G9+1-IF(G9>2,G9-2),1,2,3)","v":1}]
[null,null,null,null,null,null,1,2]
[{"$f":"Mike!C2","v":"2013-05-14"},{"$f":"EOMONTH(A11,0)","v":"2013-05-31"},{"$f":"B11-A11+1","v":18},{"$f":"C11/DAY(EOMONTH(B11,0))","v":0.58064516129032262},null,null,2,3]
[{"$f":"EOMONTH(A11,0)+1","v":"2013-06-01"},{"$f":"EOMONTH(A12,0)","v":"2013-06-30"},{"$f":"B12-A12+1","v":30},{"$f":"C12/DAY(EOMONTH(B12,0))","v":1},{"$f":"Bill!H22","v":5868.4931506849316},null,3,3]
[{"$f":"EOMONTH(A12,0)+1","v":"2013-07-01"},{"$f":"EOMONTH(A13,0)","v":"2013-07-31"},{"$f":"B13-A13+1","v":31},{"$f":"C13/DAY(EOMONTH(B13,0))","v":1},{"$f":"Bill!H17","v":5820.9677419354839},null,4,3]
[{"$f":"EOMONTH(A13,0)+1","v":"2013-08-01"},{"$f":"EOMONTH(A14,0)","v":"2013-08-31"},{"$f":"B14-A14+1","v":31},{"$f":"C14/DAY(EOMONTH(B14,0))","v":1},null,null,5,3]
[{"$f":"EOMONTH(A14,0)+1","v":"2013-09-01"},{"$f":"Mike!D2","v":"2013-09-09"},{"$f":"B15-A15+1","v":9},{"$f":"C15/DAY(EOMONTH(B15,0))","v":0.3},null,null,6,3]
[null,null,null,{"$f":"SUM(D11:D15)","v":3.8806451612903223}]
[null,null,null,{"$f":"D16*Mike!B3","v":5820.9677419354839}]