Counting and Summing 1/3 day Increments in a Date Range

[seanr2015]

Hello,


I am stumped how to write a formula that takes a cell with a value formatted as Date & Time and another cell with a value formatted as Date and Time and returns a result that is the qty of 1/3 days in the period between the two times. Any portion of a third should count as a full third in the total sum.


Days are divided into thirds as per the following 24 hour time:
00:00-08:00
08:01-16:00
16:01-23:59


Ex #1: Start Time "11/13/14 00:01" to End Time "11/14/14 09:15" = 1 2/3
Ex #2: Start Time "11/12/14 23:59" to End Time "11/14/14 14:02" = 2 1/3


Any help would be greatly appreciated!

 

[seanr2015]

Formula in C2 copied down
=INT(B2)-INT(A2)-1+(IFERROR(4-MATCH(MOD(A2,1),$E$2:$E$4),1)+IFERROR(MATCH(MOD(B2,1),$E$2:$E$4),3))/3


Upon trying this out, it appears that any start or end time that is left blank, or if both are left blank, the formula returns a result of 1/3. For example, if you drag your formula down to row 12, the result will be 1/3.

 

[Marcelo Branco]

Formula in C2 copied down
=INT(B2)-INT(A2)-1+(IFERROR(4-MATCH(MOD(A2,1),$E$2:$E$4),1)+IFERROR(MATCH(MOD(B2,1),$E$2:$E$4),3))/3


Upon trying this out, it appears that any start or end time that is left blank, or if both are left blank, the formula returns a result of 1/3. For example, if you drag your formula down to row 12, the result will be 1/3.

Try this

C2 copied down

=IF(AND(ISNUMBER(A2),ISNUMBER(B2)),INT(B2)-INT(A2)-1+(IFERROR(4-MATCH(MOD(A2,1),$E$2:$E$4),1)+IFERROR(MATCH(MOD(B2,1),$E$2:$E$4),3))/3,"")

M.

 

[seanr2015]

Thank you so much for your help! Sorry I have been slow to reply, I have been distracted with the holidays.

I tested this out only briefly but it appeared to work as I hoped! I am looking forward to finalizing the spreadsheet I am developing which will rely on this formula very heavily.

if you would be so kind, can you show me how to modify the formula if it were to apply to 1/2 days instead of 1/3 days.
the periods would be defined as:
00:01 to 12:00
12:01 to 00:00

thank you again, I REALLY appreciate your help!

 

[Marcelo Branco]

See if this is Ok

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

Start Time​

[/TD]
[TD]

End Time​

[/TD]
[TD]

Result​

[/TD]
[TD][/TD]
[TD]

PeriodStart​

[/TD]
[TD]

PeriodEnd​

[/TD]
[TD][/TD]
[TD]

Fraction​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

11/13/14 00:01​

[/TD]
[TD]

11/14/14 09:15​

[/TD]
[TD]

1,5​

[/TD]
[TD][/TD]
[TD]

00:01​

[/TD]
[TD]

12:00​

[/TD]
[TD][/TD]
[TD]

1 1/2​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

11/12/14 23:59​

[/TD]
[TD]

11/14/14 14:02​

[/TD]
[TD]

2,5​

[/TD]
[TD][/TD]
[TD]

12:01​

[/TD]
[TD]

00:00​

[/TD]
[TD][/TD]
[TD]

2 1/2​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

11/12/14 14:00​

[/TD]
[TD]

11/14/14 14:02​

[/TD]
[TD]

2,5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

2 1/2​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

11/12/14 00:01​

[/TD]
[TD]

11/14/14 14:02​

[/TD]
[TD]

3,0​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

11/13/14 00:01​

[/TD]
[TD]

11/13/14 18:00​

[/TD]
[TD]

1,0​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

11/13/14 00:01​

[/TD]
[TD]

11/13/14 15:00​

[/TD]
[TD]

1,0​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]

11/13/14 00:01​

[/TD]
[TD]

11/13/14 07:00​

[/TD]
[TD]

0,5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1/2​

[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]

12/26/14 07:00​

[/TD]
[TD]

12/29/14 14:00​

[/TD]
[TD]

4,0​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]

11/20/14 00:00​

[/TD]
[TD]

11/21/14 02:00​

[/TD]
[TD]

1,0​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]

11/21/14 00:01​

[/TD]
[TD]

11/21/14 02:00​

[/TD]
[TD]

0,5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1/2​

[/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



Formula in C2 copied down
=IF(AND(ISNUMBER(A2),ISNUMBER(B2)),INT(B2)-INT(A2)-1+(IFERROR(3-MATCH(MOD(A2,1),$E$2:$E$3),1)+IFERROR(MATCH(MOD(B2,1),$E$2:$E$3),2))/2,"")

M.

 

[seanr2015]

Thank you again!

 

[Marcelo Branco]

Thank you again!

You are welcome. Glad for helping

M.