• 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 pucha ,


I just went through your detailed explanation ; please give me some time to upload a revised workbook.


Narayan
 
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
 
Hi ,

I remembered that I had saved my files to an external hard disk !

See if these are the files.

Narayan
 

Attachments

  • Time_Division.xls
    75.5 KB · Views: 9
  • Time_Division.xlsx
    13.4 KB · Views: 8
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

  • Time_Division_to be modified.xls
    66 KB · Views: 7
You must log in or register to reply here.
Back
Top