abhishek1588
New Member
- Joined
- Feb 7, 2017
- Messages
- 1
Hi All,
I would jump directly into the problem which I have been facing.
Part-I
I have an excel sheet with tons of rows, and each row has one start time and one end time in 2 of its columns (hh:mm:ss format). These times can be anything in general. The end time is ahead of start time, obviously.
For example, in Row 1, A1 has 2:30:00pm and A2 has 2:50:00pm
What I want is to populate in A3 (and A4, A5...) is the duration in minutes elapsed between the 2 times IN EACH HOUR.
So in this first example, A3 would have the value of 20, and A4 (and onwards) be either blank or "n.a". This is easy to do if the two times belong in the same hour, but the specific problem I am facing is if the two times are separated in hours.
For example, if A1 is 2:30:00pm and A2 is 4:50:00pm, I want A3 to show 20 minutes (time elapsed in between 2:00:00pm-3:00:00pm, or in other words, in 1400 hours), A4 to show 60 minutes (time elapsed between 1500-1600 hours) and A5 to show 50 (time elapsed between 1600-1700 hours). A6 and onwards can be either blank or "n.a".
Part-II
After we have addressed the above problem, our sample sheet would look like this (example):
Row 1
Column A - Start Time
Column B - End Time
Column C (calculated before attempting part-I) - Hour of start time in 24 hour format (obtained by using =hour(start time)
Column D/E/F... (calculated) - Solution of Part-I
example (semicolons represent column change):
[Row 1] 2:40:00pm; 3:40:00pm; 14; 20; 40; "n.a/blank"
[Row 2] 8:20:00pm; 8:50:00pm; 20; 30; "n.a/blank"
What I now want is, for each row above, insert the number of blank rows below it equal to the number of non "n.a" columns in D/E/F...
So here, for row 1, we would insert 2 rows below row 1 (we have two calculated values from part-I: 20 and 40)
For row 2, we would insert 1 row below row 2 (we have one calculated value from part-I: 30)
That's about it!
Any help would be HIGHLY APPRECIATED! Thanks a ton.
I would jump directly into the problem which I have been facing.
Part-I
I have an excel sheet with tons of rows, and each row has one start time and one end time in 2 of its columns (hh:mm:ss format). These times can be anything in general. The end time is ahead of start time, obviously.
For example, in Row 1, A1 has 2:30:00pm and A2 has 2:50:00pm
What I want is to populate in A3 (and A4, A5...) is the duration in minutes elapsed between the 2 times IN EACH HOUR.
So in this first example, A3 would have the value of 20, and A4 (and onwards) be either blank or "n.a". This is easy to do if the two times belong in the same hour, but the specific problem I am facing is if the two times are separated in hours.
For example, if A1 is 2:30:00pm and A2 is 4:50:00pm, I want A3 to show 20 minutes (time elapsed in between 2:00:00pm-3:00:00pm, or in other words, in 1400 hours), A4 to show 60 minutes (time elapsed between 1500-1600 hours) and A5 to show 50 (time elapsed between 1600-1700 hours). A6 and onwards can be either blank or "n.a".
Part-II
After we have addressed the above problem, our sample sheet would look like this (example):
Row 1
Column A - Start Time
Column B - End Time
Column C (calculated before attempting part-I) - Hour of start time in 24 hour format (obtained by using =hour(start time)
Column D/E/F... (calculated) - Solution of Part-I
example (semicolons represent column change):
[Row 1] 2:40:00pm; 3:40:00pm; 14; 20; 40; "n.a/blank"
[Row 2] 8:20:00pm; 8:50:00pm; 20; 30; "n.a/blank"
What I now want is, for each row above, insert the number of blank rows below it equal to the number of non "n.a" columns in D/E/F...
So here, for row 1, we would insert 2 rows below row 1 (we have two calculated values from part-I: 20 and 40)
For row 2, we would insert 1 row below row 2 (we have one calculated value from part-I: 30)
That's about it!
Any help would be HIGHLY APPRECIATED! Thanks a ton.
