Different class time & Time samples

L

LuisVilarMoreira

New Member
Joined
Feb 15, 2018
Messages
19
Office Version
  1. 2016
Platform
  1. Windows
Hello
Thank you all to receive me in this forum.

I have a problem that i cant solve...

I have to calculate different time samples of a class time.

For example:
Class time: 00:30:00 - I need to evaluate 50% of this time in 00:03:00 samples)
Periods : 5 - (the first 3 minutes (mandatory), the last 3 minutes(mandatory), and the other 9 minuts almost equally distribute by time)
Example: 0:00:0 - 0:03:00; 00:07:00 - 00:10:00, 00:14:00 - 00:17:00, 00:20:00 - 00:23:00, 00:27:00 - 00:30:00

The class time can be from 00:30:00 until 01:00:00
that means:5 to 10 sample periods

Thank for you help
Best regards
LuÃ*s
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
Code: 
Sub sMacro1()
Dim vMinsRem, vClassTime, vPre, vPost, vSample
Dim fPct As Single
Dim vAmtFwd, vAmt, vTimeBM, vStart
Dim iBlocks As Integer, i As Integer
Dim vStartTime As Date

vStartTime = "0:00:00"
fPct = 0.5
vPre = 3
vPost = 3
vSample = 3
vClassTime = 30


vMinsRem = vClassTime
   '% to evaluate
vAmt = fPct * (vMinsRem - vPre - vPost)


Range("A1").Select
ActiveCell.Offset(0, 0).Value = "Descr"
ActiveCell.Offset(0, 1).Value = "Start"
ActiveCell.Offset(0, 2).Value = "End"
 
 'start block
Range("A2").Select
vTimeBM = DateAdd("n", vPre, vStartTime)
ActiveCell.Offset(0, 0).Value = "Start Block"
ActiveCell.Offset(0, 1).Value = "0:00:00"
ActiveCell.Offset(0, 2).Value = Format(vTimeBM, "hh:nn:ss")

vMinsRem = vMinsRem - vPre
NextRow

'sample block
iBlocks = vAmt / vSample
For i = 1 To iBlocks
    vStart = vTimeBM
    vTimeBM = DateAdd("n", vSample, vStart)


    ActiveCell.Offset(0, 0).Value = "Sample Block"
    ActiveCell.Offset(0, 1).Value = vStart
    ActiveCell.Offset(0, 2).Value = Format(vTimeBM, "hh:nn:ss")
    vMinsRem = vMinsRem - vSample
    NextRow
Next

'remaining block
vStart = vTimeBM
vMinsRem = vMinsRem - vPost
vTimeBM = DateAdd("n", vMinsRem, vStart)

ActiveCell.Offset(0, 0).Value = "Remaining Block"
ActiveCell.Offset(0, 1).Value = vStart
ActiveCell.Offset(0, 2).Value = Format(vTimeBM, "hh:nn:ss")
NextRow

'end
ActiveCell.Offset(0, 0).Value = "End Block"
ActiveCell.Offset(0, 2).Value = Format(DateAdd("n", vClassTime, vStartTime), "hh:nn:ss")
ActiveCell.Offset(0, 1).Value = Format(DateAdd("n", -vPost, ActiveCell.Offset(0, 2).Value), "hh:nn:ss")
'ActiveCell.Offset(0, 2).Value = Format(vClassTime, "hh:nn:ss")
NextRow
End Sub


Private Sub NextRow()
ActiveCell.Offset(1, 0).Select
End Sub
 
Last edited:
Upvote 0
Thank you very mutch for the code.
Work just fine.

who can i change de time?
Can i read the value of time from a cell?
I need the formula/code to work with different times, from 00:30:00 to 01:00:00.

Thanks
Luis


ranman256 said:
Code: 
Sub sMacro1()
Dim vMinsRem, vClassTime, vPre, vPost, vSample
Dim fPct As Single
Dim vAmtFwd, vAmt, vTimeBM, vStart
Dim iBlocks As Integer, i As Integer
Dim vStartTime As Date

vStartTime = "0:00:00"
fPct = 0.5
vPre = 3
vPost = 3
vSample = 3
vClassTime = 30


vMinsRem = vClassTime
   '% to evaluate
vAmt = fPct * (vMinsRem - vPre - vPost)


Range("A1").Select
ActiveCell.Offset(0, 0).Value = "Descr"
ActiveCell.Offset(0, 1).Value = "Start"
ActiveCell.Offset(0, 2).Value = "End"
 
 'start block
Range("A2").Select
vTimeBM = DateAdd("n", vPre, vStartTime)
ActiveCell.Offset(0, 0).Value = "Start Block"
ActiveCell.Offset(0, 1).Value = "0:00:00"
ActiveCell.Offset(0, 2).Value = Format(vTimeBM, "hh:nn:ss")

vMinsRem = vMinsRem - vPre
NextRow

'sample block
iBlocks = vAmt / vSample
For i = 1 To iBlocks
    vStart = vTimeBM
    vTimeBM = DateAdd("n", vSample, vStart)


    ActiveCell.Offset(0, 0).Value = "Sample Block"
    ActiveCell.Offset(0, 1).Value = vStart
    ActiveCell.Offset(0, 2).Value = Format(vTimeBM, "hh:nn:ss")
    vMinsRem = vMinsRem - vSample
    NextRow
Next

'remaining block
vStart = vTimeBM
vMinsRem = vMinsRem - vPost
vTimeBM = DateAdd("n", vMinsRem, vStart)

ActiveCell.Offset(0, 0).Value = "Remaining Block"
ActiveCell.Offset(0, 1).Value = vStart
ActiveCell.Offset(0, 2).Value = Format(vTimeBM, "hh:nn:ss")
NextRow

'end
ActiveCell.Offset(0, 0).Value = "End Block"
ActiveCell.Offset(0, 2).Value = Format(DateAdd("n", vClassTime, vStartTime), "hh:nn:ss")
ActiveCell.Offset(0, 1).Value = Format(DateAdd("n", -vPost, ActiveCell.Offset(0, 2).Value), "hh:nn:ss")
'ActiveCell.Offset(0, 2).Value = Format(vClassTime, "hh:nn:ss")
NextRow
End Sub


Private Sub NextRow()
ActiveCell.Offset(1, 0).Select
End Sub
Click to expand...
 
Upvote 0
You formula generate this

DescrStartEnd
Start Block00:00:0000:03:00
Sample Block00:03:0000:06:00
Sample Block00:06:0000:09:00
Sample Block00:09:0000:12:00
Sample Block00:12:0000:15:00
Remaining Block00:15:0000:27:00
End Block00:27:0000:30:00

<colgroup><col><col><col></colgroup><tbody>
</tbody>

i need this:
DescrStartEnd
Start Block00:00:0000:03:00
100:07:0000:10:00
200:14:0000:17:00
300:20:0000:23:00
End Block00:27:0000:30:00
or
DescrStartEnd
Start Block00:00:0000:03:00
1
2
3
4
5
6
End Block00:57:0001:00:00

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Thank you
Luis
 
Upvote 0
LuisVilarMoreira said:
Hello
Thank you all to receive me in this forum.

I have a problem that i cant solve...

I have to calculate different time samples of a class time.

For example:
Class time: 00:30:00 - I need to evaluate 50% of this time in 00:03:00 samples)
Periods : 5 - (the first 3 minutes (mandatory), the last 3 minutes(mandatory), and the other 9 minuts almost equally distribute by time)
Example: 0:00:0 - 0:03:00; 00:07:00 - 00:10:00, 00:14:00 - 00:17:00, 00:20:00 - 00:23:00, 00:27:00 - 00:30:00

The class time can be from 00:30:00 until 01:00:00
that means:5 to 10 sample periods

Thank for you help
Best regards
LuÃ*s
Click to expand...
Here the magic goes:
Code: 
=IF(ROWS($B$2:B2)>CEILING($A$1*240,1),"",TEXT(IF(ROWS($B$2:B2)>$A$1*1440-(CEILING($A$1*240,1)-1)*FLOOR((3*($A$1*480-1))/(CEILING($A$1*240,1)-1),1)-3,((ROWS($B$2:B2)-1)/480)+((ROWS($B$2:B2)-1)*$D$2)+($A$1-1/480-((CEILING($A$1*240,1)-1)*FLOOR((3*($A$1*480-1))/(CEILING($A$1*240,1)-1),1))/1440),((ROWS($B$2:B2)-1)*(1+FLOOR((3*(-1+480*$A$1))/(CEILING(240*$A$1,1)-1),1)))/1440),"hh:mm")&":00 - "&TEXT(IF(ROWS($B$2:B2)>$A$1*1440-(CEILING($A$1*240,1)-1)*FLOOR((3*($A$1*480-1))/(CEILING($A$1*240,1)-1),1)-3,((ROWS($B$2:B2)-1)/480)+((ROWS($B$2:B2)-1)*$D$2)+($A$1-1/480-((CEILING($A$1*240,1)-1)*FLOOR((3*($A$1*480-1))/(CEILING($A$1*240,1)-1),1))/1440),((ROWS($B$2:B2)-1)*(1+FLOOR((3*(-1+480*$A$1))/(CEILING(240*$A$1,1)-1),1)))/1440)+3/1440,"hh:mm")&":00")

IMPORTANT NOTES:
1) $A$1 referance cell should be in 00:30:00 format. (or you may change the reference to any cell but it must be absolute with Dollar signs)
2) B2 is the starting point in this scenario. If you want another starting cell, you must change all ROWS() like ROWS($D$2:D2)
 
Last edited by a moderator:
Upvote 0
Sorry for the late reply ...
Thank you very much for sending the formula. It works fine, but only takes the initial times, I need the final times.

Maybe I did not make myself understood.:)

I need to withdraw 50% of the duration of a PE class (it can be between 30 min and 1 hour). The remaining 3 min and the last 3 min are mandatory, the others intervals should be equally distributed throughout the class.

If class time is respected the 3-min intervals are always constant and I can program easily. I have this:

ABCDEf
1Class Time
Midle timeRemain TimeNº periodsInterval
200:45:00
0:23:000:22:00700:03:00
3
Initial TimeFinal Time


41º00:00:0000:03:00


52º00:06:0000:09:00


63º00:12:0000:15:00


74º00:19:0000:22:00


85ª00:25:0000:28:00


96º0:31:0000:34:00


107º00:37:0000:40:00


<tbody>
</tbody>

But class time may change, a teacher can finish the lesson at 00:38:00 and so the scheduled time intervals no longer serve.

I hope this explanations can help you to understand my problem.
Thank all.
Best regards,
Luis
****** id="cke_pastebin" style="position: absolute; top: 122px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">but class time may vary. A teacher can finish the lesson at 0: 38: 0 and so the scheduled time intervals no longer serve,but class time may vary. A teacher can finish the lesson at 0: 38: 0 and so the scheduled time intervals no longer serve,but class time may vary. A teacher can finish the lesson at 0: 38: 0 and so the scheduled time intervals no longer serve,but class time may vary. A teacher can finish the lesson at 0: 38: 0 and so the scheduled time intervals no longer serve,but class time may vary. A teacher can finish the lesson at 0: 38: 0 and so the scheduled time intervals no longer serve,but class time may vary. A teacher can finish the lesson at 0: 38: 0 and so the scheduled time intervals no longer serve,</body>****** id="cke_pastebin" style="position: absolute; top: 122px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">but class time may vary. A teacher can finish the lesson at 0: 38: 0 and so the scheduled time intervals no longer serve,if class time is respected the 3-min intervals are always constant and I can program easily.</body>
 
Upvote 0
LuisVilarMoreira said:
Sorry for the late reply ...
Thank you very much for sending the formula. It works fine, but only takes the initial times, I need the final times.

Maybe I did not make myself understood.:)

I need to withdraw 50% of the duration of a PE class (it can be between 30 min and 1 hour). The remaining 3 min and the last 3 min are mandatory, the others intervals should be equally distributed throughout the class.

If class time is respected the 3-min intervals are always constant and I can program easily. I have this:

ABCDEf
1Class Time
Midle timeRemain TimeNº periodsInterval
200:45:00
0:23:000:22:00700:03:00
3
Initial TimeFinal Time


41º00:00:0000:03:00


52º00:06:0000:09:00


63º00:12:0000:15:00


74º00:19:0000:22:00


85ª00:25:0000:28:00


96º0:31:0000:34:00


107º00:37:0000:40:00


<tbody>
</tbody>

But class time may change, a teacher can finish the lesson at 00:38:00 and so the scheduled time intervals no longer serve.

I hope this explanations can help you to understand my problem.
Thank all.
Best regards,
Luis
****** id="cke_pastebin" style="position: absolute; top: 122px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">but class time may vary. A teacher can finish the lesson at 0: 38: 0 and so the scheduled time intervals no longer serve,but class time may vary. A teacher can finish the lesson at 0: 38: 0 and so the scheduled time intervals no longer serve,but class time may vary. A teacher can finish the lesson at 0: 38: 0 and so the scheduled time intervals no longer serve,but class time may vary. A teacher can finish the lesson at 0: 38: 0 and so the scheduled time intervals no longer serve,but class time may vary. A teacher can finish the lesson at 0: 38: 0 and so the scheduled time intervals no longer serve,but class time may vary. A teacher can finish the lesson at 0: 38: 0 and so the scheduled time intervals no longer serve,</body>****** id="cke_pastebin" style="position: absolute; top: 122px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">but class time may vary. A teacher can finish the lesson at 0: 38: 0 and so the scheduled time intervals no longer serve,if class time is respected the 3-min intervals are always constant and I can program easily.</body>
Click to expand...

No, I am so sorry, I don't get it. But my formula diplays in "intitial time - final time" format. So if it works fine it must show both in the same cell. If you want in seperate cells, I can divide it for you.
 
Upvote 0
In your sheet
B4 =IF(ROWS($B$4:B4)>CEILING($A$2*240,1),"",TEXT(IF(ROWS($B$4:B4)>$A$2*1440-(CEILING($A$2*240,1)-1)*FLOOR((3*($A$2*480-1))/(CEILING($A$2*240,1)-1),1)-3,((ROWS($B$4:B4)-1)/480)+((ROWS($B$4:B4)-1)*$D$2)+($A$2-1/480-((CEILING($A$2*240,1)-1)*FLOOR((3*($A$2*480-1))/(CEILING($A$2*240,1)-1),1))/1440),((ROWS($B$4:B4)-1)*(1+FLOOR((3*(-1+480*$A$2))/(CEILING(240*$A$2,1)-1),1)))/1440),"hh:mm\:\0\0"))

C4 =IF(ROWS($C$4:C4)>CEILING($A$2*240,1),"",TEXT(IF(ROWS($C$4:C4)>$A$2*1440-(CEILING($A$2*240,1)-1)*FLOOR((3*($A$2*480-1))/(CEILING($A$2*240,1)-1),1)-3,((ROWS($C$4:C4)-1)/480)+((ROWS($C$4:C4)-1)*$D$2)+($A$2-1/480-((CEILING($A$2*240,1)-1)*FLOOR((3*($A$2*480-1))/(CEILING($A$2*240,1)-1),1))/1440),((ROWS($C$4:C4)-1)*(1+FLOOR((3*(-1+480*$A$2))/(CEILING(240*$A$2,1)-1),1)))/1440)+3/1440,"hh:mm\:\0\0"))

Or

C4 simply = B4+(3/1440)
 
Last edited by a moderator:
Upvote 0
More clean way:

B4 =IF(ROWS($B$4:B4)>CEILING($A$2*240,1),"",IF(ROWS($B$4:B4)>$A$2*1440-(CEILING($A$2*240,1)-1)*FLOOR((3*($A$2*480-1))/(CEILING($A$2*240,1)-1),1)-3,((ROWS($B$4:B4)-1)/480)+((ROWS($B$4:B4)-1)*$D$2)+($A$2-1/480-((CEILING($A$2*240,1)-1)*FLOOR((3*($A$2*480-1))/(CEILING($A$2*240,1)-1),1))/1440),((ROWS($B$4:B4)-1)*(1+FLOOR((3*(-1+480*$A$2))/(CEILING(240*$A$2,1)-1),1)))/1440))

and

C4 =IF(B4<>"",B4+(3/1440),"")
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Help with rank and difference.
Replies
3
Views
56
Fluff
Fluff
C
  • Question
Calculate and Identify Ordinary Hours, Double time or 1.5 Time
Replies
0
Views
62
ChetanPuri
C
K
  • Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
305
awoohaw
awoohaw
D
  • Question
Rounding Negative time to the nearest minute in Excel
Replies
4
Views
69
Scott Huish
Scott Huish
C
  • Solved
Calculate difference b/w numbers
Replies
3
Views
127
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,642
Messages
6,120,701
Members
448,980
Latest member
CarlosWin

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top