Need help with a Formula

  • Thread starter Thread starter Scott
  • Start date Start date
S

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
 
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.
 
Are you asking what format it is in??

Scott

OssieMac said:
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


Scott said:
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
Click to expand...
Click to expand...
 
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


Scott said:
Are you asking what format it is in??

Scott

OssieMac said:
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


Scott said:
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
Click to expand...
Click to expand...
Click to expand...
 
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
Click to expand...

Your explanation and examples are inconsistent.
if the start time is before 9:00 a.m. then the space
should be blank;
Click to expand...

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