Date and Time Calculation Based on Sun Raise and End of Night

[mba_110]

Hi,

I need to calculate day and night time based on Sun Raise and Sun Set, since both time are not starting and ending same time, it creates time gap.

Also, it is important to see which side is greater weather its DAY TIME is longer than NIGHT TIME, the total of both sides should be 24 hours, since DAY has its boundaries of Sun raise and set its very clear but the problem is with night time which need to be calculated correctly in formula.

Mirza Bahadur Ali.xlsx
	C	D	E	F	G	H	I	J
6	0:00:00	One Star Day		Day Time	5:45:00 PM	Sun Set	6:22:00 AM	Sun Rise
7	0:00:00	One Star Night		Night Time	6:21:00 AM	Night Ends	5:46:00 PM	Night Starts
Saat Malum Karna

Cell Formulas
Range	Formula
C6:C7	C6	=E6/12
G7	G7	=I6-TIME(,1,)
I7	I7	=G6+TIME(,1,)

In this case, Sunraise and sunset time's are constant, the only problem is the gap between them which is at early morning time, in above example if you see the Suraise time is 6:22 AM and Sunset is 5:45 PM which total : 11:23:00 the day leaves the gap of 37 minutes to complete the 12 hours, i need this 37 minutes to be added in night time, when i try to substract "Night End Time" from Sunset time its gave me error because it will be something we substracting AM from PM time.

Anyhow, to avoid this problem in future i want to use IF function to determine which side DAY or NIGHT is longer time than add the remaining time to its opposite side i.e (if day is longer and night time is short than remaining night time to be added in day time and vise versa for night time)

I hope its clarifies the issue.

regards,
MBA

 

[Bosquedeguate]

For day time
I have used this calculation
Sunset time - Sunrise time
Using a 24 hour clock
17:45 - 06:22

For night time hours
(23:59 - 17:45 + 00:01) + 06:22

Format your cells as time hh:mm

Does that help?

 

[mba_110]

For day time
I have used this calculation
Sunset time - Sunrise time
Using a 24 hour clock
17:45 - 06:22

For night time hours
(23:59 - 17:45 + 00:01) + 06:22

Format your cells as time hh:mm

Does that help?

I need to add seconds also, and it needs proper formula i can't put values manually, its very simple to do manually but i need to make it in formula, as i mentioned earlier i do not have issues with DAY time the problem only with night time.

 

[Bosquedeguate]

I need to add seconds also, and it needs proper formula i can't put values manually, its very simple to do manually but i need to make it in formula, as i mentioned earlier i do not have issues with DAY time the problem only with night time.

I think the format I suggested will give what your need. Just enter times as hh:mm:ss
for night hours
23:59:59 - 17:45:00 + 00:00:01 + 06:22:00

 

[mba_110]

I think the format I suggested will give what your need. Just enter times as hh:mm:ss
for night hours
23:59:59 - 17:45:00 + 00:00:01 + 06:22:00

I put this condition in cell E7 =23:59:59- G7+00:00:01+ I7 and its gave me error of formula, Error is "There is an error with formula, not trying to type formula", excel thinks starting from equal sign is formula, as per your guidance I have change the format of all time cells to hh:mm:ss.

If you are asking me to enter the values of what you have suggested, that will be manual input and each time i have to modify it manually.

 

[Bosquedeguate]

I think the format I suggested will give what your need. Just enter times as hh:mm:ss
for night hours
23:59:59 - 17:45:00 + 00:00:01 + 06:22:00

Use a formula like the example below

Book4
	A	B	C	D
1	sunrise	sunset	Day Hrs	Night Hrs
2	6:22:00	17:45:00	11:23:00	12:37:00
Sheet1

Cell Formulas
Range	Formula
C2	C2	=B2-A2
D2	D2	=TIME(23,59,59) -B2+A2 + TIME(0,0,1)

 

[mba_110]

Use a formula like the example below

Book4
	A	B	C	D
1	sunrise	sunset	Day Hrs	Night Hrs
2	6:22:00	17:45:00	11:23:00	12:37:00
Sheet1

Cell Formulas
Range	Formula
C2	C2	=B2-A2
D2	D2	=TIME(23,59,59) -B2+A2 + TIME(0,0,1)

That work perfect, i am glad thanks buddy.