• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Time Division

P

pucha

Member
Hi everybody,


Friends I would like to know how the time division formula used to divide time in excel. Like the time from 6.00 am to 6.00 pm diving it into 5 equal time period.


Example 06:24:02 hrs to 19:01:21 hrs is equal to 02:31:28 hrs each if divided by 5times. This is one part.


Second part after getting the result, now I want the toal time i.e. (02:31:28, which is actually from 06:24:02 to 08:55:30 time duration, 1 part) to covert into total minutes i.e. in this case = 150.07 (hope so) then divide the total minute by 12, 30, 36, 48, & 18 times to get below result:-


00:12:37

00:31:33

00:37:52

00:50:29

00:18:56


Similarly for remaining 4 parts of the above main time.


Will you please tell me the Excel formula for doing so.


Regards

pucha
 
Hi pucha ,


Can you see the file here ?


http://speedy.sh/Nqaz3/Time-Division.xlsx


I am not able to understand your second part ; you say similarly for the remaining 4 parts ; however , all 5 parts are of the same duration , so the result for the first part will be repeated for all the other 4 parts.


Narayan
 
Hi Narayank991,


Yes, you are right, the result for the first part will be repeated for all the other 4 parts.


Will you please give the link for "xls" file as I am using excel 2003.


Thanks

Pucha
 
Hi Narayank991,


Thankyou, I manage to convert xlsx to xls. Thankyou for the xlsx file.


Now would you please help me calculate the second part.


Thanks

pucha
 
Hi pucha ,


I am not sure whether this is what you want ; check this file here :


http://speedy.sh/3eSPQ/Time-Division.xls


Narayan
 
Hi Narayank991,


No sir, Only the first one is correct. But the second, third, fourth & fifth are not the same as below.


you gave in your xls as given below:

12- 00:12:37

30- 00:05:03

36- 00:04:12

48- 00:03:09

18- 00:08:25


But it will be as below:

12- 00:12:37

30- 00:31:33

36- 00:37:52

48- 00:50:29

18- 00:18:56


I think it is 12, 30, 36, 48 & 18 times the total time period(02:31:28)

Thanks

pucha
 
Hi pucha ,


I am not able to understand ; the formula for the first one ( 12 ) is :


=($B2-$B1)/D$4


where B2 is the END time , B1 is the START time , and D4 contains 12 , which is the number of parts.


When the number of parts is 30 , how do you get 00:31:33 ? Can you please explain ?


What I did was put in the following formula in E5 : =D5*E$4/D$4. Copy this across.


Narayan
 
Hi Narayank991,


Thank for so far.

Let me tell you what I want to do. I want to divide the duration of sunrise to sunset & from sunset to next day sunrise into five equal parts i,e, total 10 parts.

Today's sunrise & sunset are:

sunrise time - 06:21:35

sunset time - 17:09:08

sunset time - 17:09:08

next day sunrise time - 06:22:03

---------------------------------

FIRST

Now sunrise time - 06:21:35 to sunset time - 17:09:08 (divide into 5 parts)

First part-06:21:35--08:31:06---02:09:31(difference of time)

Next part-08:31:06--10:40:36---02:09:31(difference of time)

Next part-10:40:36--12:50:07---02:09:31(difference of time)

Next part-12:50:07--14:59:37---02:09:31(difference of time)

Next part-14:59:37--17:09:08---02:09:31(difference of time)

SECONDLY

In first part I want to divide the time period i.e (06:21:35-08:31:06) into again 5 parts with different minutes (12, 30, 36, 48, & 18)mts. Like to get the result below:

start-(minutes/sec added)-

06:21:35--00:10:48--06:32:23---------1 (formula)

06:32:23--00:26:59--06:59:21

06:59:21--00:32:23--07:31:44

07:31:44--00:43:10--08:14:54

08:14:54--00:16:11--08:31:06

---------------------------------------

08:31:06--00:10:48--08:41:53

08:41:53--00:26:59--09:08:52

09:08:52--00:32:23--09:41:15

09:41:15--00:43:10--10:24:25

10:24:25--00:16:11--10:40:36

----------------------------------------

like this 3 more parts.......upto 17:09:08 sunset.

------------------------------------------

(formula)- to get the minute/sec added is as follow-

(difference of time)=02:09:31

adding the minutes =12+30+36+48+18=144 mts

=(02:09:31)/144 x 12 (first minute)= 00:10:48-------

Now adding the sunrise time 06:21:35 + 00:10:48 = 06:32:23---------1 (formula)


Like this all are calculate upto 17:09:08 sunset in 5 parts


Similarly from 17:09:08 sunset to next day sunrise 06:22:03, The process is same. But this time the minutes will be (30+24+30+24+36=144). So to get the next day sunrise time 06:22:03 from 17:09:08 sunset time I have to divide the time into 5 equal parts and further each parts will also be divided into sub parts with respect to the minutes (30+24+30+24+36=144) as above.


The problem is I am getting the sunrise to sunset time correctly but from sunset to next day sunrise time is not correct as per above process. i.e. the time 06:22:03.


May be I think it is due to next day Excel calculation or something else, out of my mind.


Please guide

Respect

pucha
 
Hi Narayank991,


One more question.

As the above problem has been solved by you, I want to now if you can help me to highlight the row with the system time. As whenever the excel file opens it will color/highlight the row as per the system time. i.e.- if the system time is 06:40:25 then it will highlight the second row (row2) in below sample.


06:21:35--00:10:48--06:32:23-----row1

06:32:23--00:26:59--06:59:21-----row2

06:59:21--00:32:23--07:31:44-----row3

07:31:44--00:43:10--08:14:54-----row4

08:14:54--00:16:11--08:31:06-----row5


Regards

pucha
 
Hi Narayank991,


Thank you once again. The excel file is working perfectly. Brilliant.


"Sorry to reply late. I went for a vacation as my daughter's exam was over."


Regards

pucha
 
Hi Narayank991,

Thank you for the above excel file "Time-Division.xls", which I was using regularly, but unfortunately last week I had a difficulty with my hard disk, It crashed and had to replace with a new one. I missed all my files their.

Will you please help me to send the excel file once again if you had, otherwise I had to make it from the beginning.

With Regards
pucha
 
Hi ,

I am sorry , but I don't seem to have the file with me. I would have it on a second PC that I have , but that went on the blink about a month back , and I have not yet got down to sending it for repair. If I could get that PC back in working order , I might be able to send you the file.

I am sure that if you can upload your workbook with the data in it , the formulae can be redone.

Narayan
 
Thanks Narayank991,

I am trying to load a workbook with data as the original has gone with my crashed hard disk.

pucha
 
Yes, Thank you. Thank you once again for keeping those files.

But as those files were created by you somewhere in dec 9, 2012. Later I had modified it as per my use. Now the modified file is no longer with me.

Will you please check the file I am uploading for your reference and the output I want from it.

Thanks
pucha
 

Attachments

You must log in or register to reply here.
Back
Top