Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with a Formula
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with a Formula
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" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with a Formula
Are you asking what format it is in??
Scott "OssieMac" wrote: 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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with a Formula
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" wrote: Are you asking what format it is in?? Scott "OssieMac" wrote: 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" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with a Formula
On Fri, 16 Jan 2009 09:26:27 -0800, Scott
wrote: 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,(AN D(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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Regression Leverage Formula (Jerry W. Lewis or Mike Middleton)already have DFITS formula | Excel Worksheet Functions | |||
Excel 2002 Formula: Urgent Conditional Formula Required Right Away - if possible | Excel Discussion (Misc queries) | |||
Formula expected end of statement error, typing formula into cell as part of VBA macro | Excel Programming | |||
Cell doesn't show formula result - it shows formula (CTRL + ' doe. | Excel Worksheet Functions | |||
Commenting custom formula fields/formula on formula editor | Excel Programming |