Need help with a Formula

[Scott]

I have a lengthy formula where I solve one problem by changing the formula
and another keeps seeming to occur. In the last column shown in the data
below I am trying to calculate the amount of time that has passed between the
stop time of one row and the start time of the next row but there are a lot
of IF's. For example: if the start time is before 9:00 a.m. then the space
should be blank; if the start time is between 9:00 a.m. and 10:00 a.m. then
it should be subtracted by 9:00 instead of the previous Stop time; if the
hour is <4 then use the formula ROUND(MOD(A3-B2),1)*1440,0) instead; if it is
the first entry or there is no entry then the space in column D should be
blank. The current formula I have in column D is as follows:

=IF(OR(A1<>"Start",A2<>""),(IF(OR(HOUR(A2)>=9,(AND(HOUR(A2)<4,MIN(A2)>0))),ROUND(MOD(A2-MAX(R2,"9:00"),1)*1440,0),0)),0)

This formula works until I have two entries that occur after midnight as you
can see below. I haven't been able to insert the ROUND(MOD(A3-B2),1)*1440,0)
formula for HOUR < 4 without getting an error.

Any help would be greatly appreciated. Plus if there is a way to shorten
parts of the formula that I have so far that would be great. I think I am
making this more complicated than it should be.

Thanks,

Scott

A B C D
Start Stop Time
7:20 7:36 0:16
8:34 8:45 0:11
9:45 9:52 0:07 45
10:20 10:31 0:11 28
11:02 11:12 0:10 31
12:15 12:27 0:12 63
13:07 13:54 0:47 40
14:20 15:04 0:44 26
17:47 18:00 0:13 163
18:44 18:51 0:07 44
19:15 19:37 0:22 24
20:34 20:42 0:08 57
22:04 22:43 0:39 82
23:33 23:38 0:05 50
1:00 1:10 0:10 82

 

[OssieMac]

Hi Scott,

I tried to emulate your worksheet but ran into a problem. What is the value
of Start?

My first thoughts are can you use 25:00 and 25:10 in lieu of 1:00 and 1:10?
You would need to format the column with the square brackets around the hrs
if you use this method. Format example [h]:mm

I will be out most of today so if anyone else can help Scott after he
answers the above questions then feel free to do so.

 

[Scott]

Are you asking what format it is in??

Scott

Hi Scott,

I tried to emulate your worksheet but ran into a problem. What is the value
of Start?

My first thoughts are can you use 25:00 and 25:10 in lieu of 1:00 and 1:10?
You would need to format the column with the square brackets around the hrs
if you use this method. Format example [h]:mm

I will be out most of today so if anyone else can help Scott after he
answers the above questions then feel free to do so.

--
Regards,

OssieMac

I have a lengthy formula where I solve one problem by changing the formula
and another keeps seeming to occur. In the last column shown in the data
below I am trying to calculate the amount of time that has passed between the
stop time of one row and the start time of the next row but there are a lot
of IF's. For example: if the start time is before 9:00 a.m. then the space
should be blank; if the start time is between 9:00 a.m. and 10:00 a.m. then
it should be subtracted by 9:00 instead of the previous Stop time; if the
hour is <4 then use the formula ROUND(MOD(A3-B2),1)*1440,0) instead; if it is
the first entry or there is no entry then the space in column D should be
blank. The current formula I have in column D is as follows:

=IF(OR(A1<>"Start",A2<>""),(IF(OR(HOUR(A2)>=9,(AND(HOUR(A2)<4,MIN(A2)>0))),ROUND(MOD(A2-MAX(R2,"9:00"),1)*1440,0),0)),0)

This formula works until I have two entries that occur after midnight as you
can see below. I haven't been able to insert the ROUND(MOD(A3-B2),1)*1440,0)
formula for HOUR < 4 without getting an error.

Any help would be greatly appreciated. Plus if there is a way to shorten
parts of the formula that I have so far that would be great. I think I am
making this more complicated than it should be.

Thanks,

Scott

A B C D
Start Stop Time
7:20 7:36 0:16
8:34 8:45 0:11
9:45 9:52 0:07 45
10:20 10:31 0:11 28
11:02 11:12 0:10 31
12:15 12:27 0:12 63
13:07 13:54 0:47 40
14:20 15:04 0:44 26
17:47 18:00 0:13 163
18:44 18:51 0:07 44
19:15 19:37 0:22 24
20:34 20:42 0:08 57
22:04 22:43 0:39 82
23:33 23:38 0:05 50
1:00 1:10 0:10 82

 

[OssieMac]

Hi again Scott,

Still here for a few minutes but going out after posting this. My error on
the Start. I was thinking that it was a named range now I see that you are
just eliminating the column header. However, I think that my problem is
really the values in column R for the MAX formula bit that I have not got and
therefore the formula does not return the correct values for me to test.

--
Regards,

OssieMac

Are you asking what format it is in??

Scott

Hi Scott,

I tried to emulate your worksheet but ran into a problem. What is the value
of Start?

My first thoughts are can you use 25:00 and 25:10 in lieu of 1:00 and 1:10?
You would need to format the column with the square brackets around the hrs
if you use this method. Format example [h]:mm

I will be out most of today so if anyone else can help Scott after he
answers the above questions then feel free to do so.

--
Regards,

OssieMac

I have a lengthy formula where I solve one problem by changing the formula
and another keeps seeming to occur. In the last column shown in the data
below I am trying to calculate the amount of time that has passed between the
stop time of one row and the start time of the next row but there are a lot
of IF's. For example: if the start time is before 9:00 a.m. then the space
should be blank; if the start time is between 9:00 a.m. and 10:00 a.m. then
it should be subtracted by 9:00 instead of the previous Stop time; if the
hour is <4 then use the formula ROUND(MOD(A3-B2),1)*1440,0) instead; if it is
the first entry or there is no entry then the space in column D should be
blank. The current formula I have in column D is as follows:

=IF(OR(A1<>"Start",A2<>""),(IF(OR(HOUR(A2)>=9,(AND(HOUR(A2)<4,MIN(A2)>0))),ROUND(MOD(A2-MAX(R2,"9:00"),1)*1440,0),0)),0)

This formula works until I have two entries that occur after midnight as you
can see below. I haven't been able to insert the ROUND(MOD(A3-B2),1)*1440,0)
formula for HOUR < 4 without getting an error.

Any help would be greatly appreciated. Plus if there is a way to shorten
parts of the formula that I have so far that would be great. I think I am
making this more complicated than it should be.

Thanks,

Scott

A B C D
Start Stop Time
7:20 7:36 0:16
8:34 8:45 0:11
9:45 9:52 0:07 45
10:20 10:31 0:11 28
11:02 11:12 0:10 31
12:15 12:27 0:12 63
13:07 13:54 0:47 40
14:20 15:04 0:44 26
17:47 18:00 0:13 163
18:44 18:51 0:07 44
19:15 19:37 0:22 24
20:34 20:42 0:08 57
22:04 22:43 0:39 82
23:33 23:38 0:05 50
1:00 1:10 0:10 82

 

[Ron Rosenfeld]

I have a lengthy formula where I solve one problem by changing the formula
and another keeps seeming to occur. In the last column shown in the data
below I am trying to calculate the amount of time that has passed between the
stop time of one row and the start time of the next row but there are a lot
of IF's. For example: if the start time is before 9:00 a.m. then the space
should be blank; if the start time is between 9:00 a.m. and 10:00 a.m. then
it should be subtracted by 9:00 instead of the previous Stop time; if the
hour is <4 then use the formula ROUND(MOD(A3-B2),1)*1440,0) instead; if it is
the first entry or there is no entry then the space in column D should be
blank. The current formula I have in column D is as follows:

=IF(OR(A1<>"Start",A2<>""),(IF(OR(HOUR(A2)>=9,(AND(HOUR(A2)<4,MIN(A2)>0))),ROUND(MOD(A2-MAX(R2,"9:00"),1)*1440,0),0)),0)

This formula works until I have two entries that occur after midnight as you
can see below. I haven't been able to insert the ROUND(MOD(A3-B2),1)*1440,0)
formula for HOUR < 4 without getting an error.

Any help would be greatly appreciated. Plus if there is a way to shorten
parts of the formula that I have so far that would be great. I think I am
making this more complicated than it should be.

Thanks,

Scott

A B C D
Start Stop Time
7:20 7:36 0:16
8:34 8:45 0:11
9:45 9:52 0:07 45
10:20 10:31 0:11 28
11:02 11:12 0:10 31
12:15 12:27 0:12 63
13:07 13:54 0:47 40
14:20 15:04 0:44 26
17:47 18:00 0:13 163
18:44 18:51 0:07 44
19:15 19:37 0:22 24
20:34 20:42 0:08 57
22:04 22:43 0:39 82
23:33 23:38 0:05 50
1:00 1:10 0:10 82

Your explanation and examples are inconsistent.

if the start time is before 9:00 a.m. then the space
should be blank;

But in your last line, 1:00 is before 9:00 a.m., yet you are showing 82 minutes
--ron