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!

 

[Marcelo Branco]

Welcome to Mr Excel

Not sure if i understand what you need.

A question: shouldn't the result of Ex #2 be 2.00 instead of 2 1/3

1 period in day 12 = 1/3
3 periods in day 13 = 3/3
2 periods in day 14 = 2/3

1/3 + 3/3 + 2/3 = 2.00

Or am i missing something?

M.

 

[seanr2015]

You are correct, that was my mistake. Also worth mentioning is I am using Office 2011 for Mac. Please help, I've been struggling with this for a while! Thank you.

 

[Marcelo Branco]

Maybe something like this


[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,6667​

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

00:00​

[/TD]
[TD]

08:00​

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

1 2/3​

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

3​

[/TD]
[TD]

11/12/14 23:59​

[/TD]
[TD]

11/14/14 14:02​

[/TD]
[TD]

2,0000​

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

08:01​

[/TD]
[TD]

16:00​

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

2​

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

4​

[/TD]
[TD]

11/12/14 14:00​

[/TD]
[TD]

11/14/14 14:02​

[/TD]
[TD]

2,3333​

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

16:01​

[/TD]
[TD]

23:59​

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

2 1/3​

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

5​

[/TD]
[TD]

11/12/14 00:01​

[/TD]
[TD]

11/14/14 14:02​

[/TD]
[TD]

2,6667​

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

2 2/3​

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

6​

[/TD]
[TD]

11/13/14 00:01​

[/TD]
[TD]

11/13/14 23:59​

[/TD]
[TD]

1,0000​

[/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]

0,6667​

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

2/3​

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

8​

[/TD]
[TD]

11/13/14 00:01​

[/TD]
[TD]

11/13/14 07:00​

[/TD]
[TD]

0,3333​

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

1/3​

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

9​

[/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
=INT(B2)-INT(A2)-1+(4-MATCH(MOD(A2,1),$E$2:$E$4)+MATCH(MOD(B2,1),$E$2:$E$4))/3

If you want fraction formatting see column H

Hope this helps

M.

 

[seanr2015]

This is great, thank you!

In playing around with it, it did notice one thing. If I set the time periods as:
00:01 - 08:00
08:01 - 16:00
16:01 - 24:00 (00:00)

Then log a start and end time, where the end time equals midnight, the result is "#N/A".
Are you aware of a way that I can make this work? We will have times when the time entered will be midnight and would prefer that it count as the final third of a day as opposed to the first third of a day.

 

[Marcelo Branco]

This is great, thank you!

In playing around with it, it did notice one thing. If I set the time periods as:
00:01 - 08:00
08:01 - 16:00
16:01 - 24:00 (00:00)

Then log a start and end time, where the end time equals midnight, the result is "#N/A".
Are you aware of a way that I can make this work? We will have times when the time entered will be midnight and would prefer that it count as the final third of a day as opposed to the first third of a day.

Could you provide some examples with Start Time and/or End Time 00:00 and expected results?

M.

 

[seanr2015]

11/18/14 22:30 to 11/19/14 00:00 should equal 1 1/3 but results in "#N/A"
11/21/14 00:00 to 11/21/14 02:00 should equal 1/3 but results in "#N/A"

The formula I am using is:
=IF(D18="N/A",0,INT(E18)-INT(D18)-1+(4-MATCH(MOD(D18,1),$AD$8:$AD$10)+MATCH(MOD(E18,1),$AD$8:$AD$10))/3)

The intent is for the formula to look at two cells and if either of them ="N/A", the formula returns "0", otherwise it returns the sum of the 1/3 days.

I've tried defining midnight for the ending period of the final 1/3 of the day by using both 00:00 and as 24:00, both ways result in the same issue.

 

[Marcelo Branco]

Still not clear for me

You wrote

Are you aware of a way that I can make this work? We will have times when the time entered will be midnight and would prefer that it count as the final third of a day as opposed to the first third of a day.

Doing so, in your second example (see below) the Start Time is greater than the End Time (???)

11/21/14 00:00 to 11/21/14 02:00 should equal 1/3 but results in "#N/A"

Or do you want to consider 00:00 equal 24:00 only in End Time, not in Start Time?

If so, try this


[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]
[TD]

I​

[/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]
[TD][/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

11/13/14 00:01​

[/TD]
[TD]

11/14/14 09:15​

[/TD]
[TD]

1,6667​

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

00:01​

[/TD]
[TD]

08:00​

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

1 2/3​

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

3​

[/TD]
[TD]

11/12/14 23:59​

[/TD]
[TD]

11/14/14 14:02​

[/TD]
[TD]

2,0000​

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

08:01​

[/TD]
[TD]

16:00​

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

2​

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

4​

[/TD]
[TD]

11/12/14 14:00​

[/TD]
[TD]

11/14/14 14:02​

[/TD]
[TD]

2,3333​

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

16:01​

[/TD]
[TD]

00:00​

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

2 1/3​

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

5​

[/TD]
[TD]

11/12/14 00:01​

[/TD]
[TD]

11/14/14 14:02​

[/TD]
[TD]

2,6667​

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

2 2/3​

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

6​

[/TD]
[TD]

11/13/14 00:01​

[/TD]
[TD]

11/13/14 18:00​

[/TD]
[TD]

1,0000​

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

1​

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

7​

[/TD]
[TD]

11/13/14 00:01​

[/TD]
[TD]

11/13/14 15:00​

[/TD]
[TD]

0,6667​

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

2/3​

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

8​

[/TD]
[TD]

11/13/14 00:01​

[/TD]
[TD]

11/13/14 07:00​

[/TD]
[TD]

0,3333​

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

1/3​

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

9​

[/TD]
[TD]

11/18/14 22:30​

[/TD]
[TD]

11/19/14 00:00​

[/TD]
[TD]

1,3333​

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

1 1/3​

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

10​

[/TD]
[TD]

11/21/14 00:00​

[/TD]
[TD]

11/21/14 02:00​

[/TD]
[TD]

0,3333​

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

1/3​

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

11​

[/TD]
[TD]

11/20/14 00:00​

[/TD]
[TD]

11/21/14 02:00​

[/TD]
[TD]

1,3333​

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

1 1/3​

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

12​

[/TD]
[TD][/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
=INT(B2)-INT(A2)-1+(IFERROR(4-MATCH(MOD(A2,1),$E$2:$E$4),3)+IFERROR(MATCH(MOD(B2,1),$E$2:$E$4),3))/3

M.

 

[seanr2015]

I'm sorry, I am getting confused by this and am not used to dealing with 24 hour time.

My second example was not correctly shown. It should have been shown as:
11/21/14 00:01 to 11/21/14 02:00 should equal to 1/3

Also, for reference:
11/20/14 24:00 to 11/21/14 02:00 equal to 2/3

The overall intent is:
24:00 is the last time recorded for any day (I've been using 00:00 to refer to this but would prefer it display as 24:00, but not sure if that is possible)
00:01 is the first time recorded for any day

 

[Marcelo Branco]

See if this is what you want


[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,6667​

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

00:01​

[/TD]
[TD]

08:00​

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

1 2/3​

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

3​

[/TD]
[TD]

11/12/14 23:59​

[/TD]
[TD]

11/14/14 14:02​

[/TD]
[TD]

2,0000​

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

08:01​

[/TD]
[TD]

16:00​

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

2​

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

4​

[/TD]
[TD]

11/12/14 14:00​

[/TD]
[TD]

11/14/14 14:02​

[/TD]
[TD]

2,3333​

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

16:01​

[/TD]
[TD]

00:00​

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

2 1/3​

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

5​

[/TD]
[TD]

11/12/14 00:01​

[/TD]
[TD]

11/14/14 14:02​

[/TD]
[TD]

2,6667​

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

2 2/3​

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

6​

[/TD]
[TD]

11/13/14 00:01​

[/TD]
[TD]

11/13/14 18:00​

[/TD]
[TD]

1,0000​

[/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]

0,6667​

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

2/3​

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

8​

[/TD]
[TD]

11/13/14 00:01​

[/TD]
[TD]

11/13/14 07:00​

[/TD]
[TD]

0,3333​

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

1/3​

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

9​

[/TD]
[TD]

11/18/14 22:30​

[/TD]
[TD]

11/19/14 00:00​

[/TD]
[TD]

1,3333​

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

1 1/3​

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

10​

[/TD]
[TD]

11/21/14 00:01​

[/TD]
[TD]

11/21/14 02:00​

[/TD]
[TD]

0,3333​

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

1/3​

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

11​

[/TD]
[TD]

11/20/14 00:00​

[/TD]
[TD]

11/21/14 02:00​

[/TD]
[TD]

0,6667​

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

2/3​

[/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
=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.