Recording Hours of Sleep - Excel Template

[Sundance_Kid]

Hey,

I want to start recording the amount of sleep I get each night based on the output from my watch and track it over a period to see is it improving (which it needs to).

I would want to record it in the following categories:
1. Night Sleep - record in hours and minutes, so for example 7hr 23 mins
2. Restful Sleep - record in hours and minutes, so for example 5hr 44 mins
3. Restless Sleep - record in hours and minutes, so for example 1hr 39 mins
4. Restful Sleep - 78%

I am mindful of the fact that there are 60 minutes in an hour compared to say a standard 100 units/per cent when it comes to numbers.

I would like to have a graph which will over time show the improvement (hopefully) in my sleep.

So is there a best format to enter the time in so that it is easy to enter and easy then for excel to convert it when it comes to creating a graph.

Thanks

 

[RAJESH NATH]

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2			HOURS TO MIN	60
3
4		TOTAL				RESTFULL				RESTLESS
5	NIGHT	hour	min	TOTAL MIN	TOTAL	hour	min	TOTAL MIN	PERCDNTAGE	hour	min	TOTAL MIN	PERCENTAGE
6	1	7	23	443	100%	5	44	344	77.65	1	39	99	22.35
7	2	8	2	482	100%	6	44	404	83.82	1	18	78	16.18
8	3			0	100%			0				0
9	4			0	100%			0				0
10	5			0	100%			0				0
11	6			0	100%			0				0
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
Sheet1

Cell Formulas
Range	Formula
H6:H11	H6	=(F6*$D$2)+G6
I6:I11	I6	=IFERROR(ROUND((H6/D6)*100,2)," ")
L6:L11	L6	=J6*$D$2+K6
M6:M11	M6	=IFERROR(ROUND((L6/D6)*100,2)," ")
D6:D11	D6	=($D$2*B6)+C6

CLICK ON INSERT>SELECT CHART (IT SHOWS BLANK BOX AFTER CLICKING CHART)>RIGHT CORNER OF BOX IT APPEAR A FILTER CLICK ON FILTER>IN BOTTOM IT SHOWS SELECT DATA> CLICK ON DATA( IT APPEAR A BOX)>IN BOX CLICK ON ADD>CLICK UP ARROW IN SERIES VALUE>SELECT RANGE, BY THIS WAY SELECT AGAIN ANOTHER RANGE. THEN YOU WILL WHAT YOU WANT

 

[Dave Patton]

It may be just a personal preference.
Try the 2 alternatives below.

T202008c.xlsm
	A	B	C	D	E	F
1	Date	Restful	Restless	Night	Awake	% Restful
2	20-Aug-20	5:44:00	1:39	7:23:00		77.65%
3
4	20-Aug-20	5.73	1.65	7.38		77.65%
5
6
Sleep

Cell Formulas
Range	Formula
D2,D4	D2	=B2+C2
F2,F4	F2	=B2/D2
B4	B4	=5+44/60
C4	C4	=1+39/60

 

[Dave Patton]

or

T202008c.xlsm
	A	B	C	D	E	F	G	H
6	Date	Restful		Restless		Night	Awake	% Restful
7	20-Aug-20	5	44	1	39	7:23:00		77.65%
8
9	20-Aug-20	5	44	1	39	7.38		77.65%
Sleep

Cell Formulas
Range	Formula
F7	F7	=TIME(B7,C7,0)+TIME(D7,E7,0)
H7	H7	=TIME(B7,C7,0)/F7
F9	F9	=B9+C9/60+D9+E9/60
H9	H9	=(B9+C9/60)/F9

 

[Sundance_Kid]

Thanks both

Was hoping to be able to have just one row for each date & not a second row converting it. Or having to split hours and minutes out.

Maybe the above ideas are the only options.

Thanks

 

[Dave Patton]

I do not follow your comment.
" Was hoping to be able to have just one row for each date & not a second row converting it. Or having to split hours and minutes out. "

I provided alternatives depending on you prefer to enter the data.

Possibly your watch etc. logs your sleep information of possibly you can export the data into your spreadsheet.

 

[RAJESH NATH]

Thank you for feedback ?

 

[MARK858]

Was hoping to be able to have just one row for each date & not a second row converting it. Or having to split hours and minutes out.

Perhaps it would be an idea to post a manually created example of your layout including how you want the times inputted and the result you want to achieve using the boards XL2BB addin.

 

[Sundance_Kid]

I do not follow your comment.
" Was hoping to be able to have just one row for each date & not a second row converting it. Or having to split hours and minutes out. "

I provided alternatives depending on you prefer to enter the data.

Possibly your watch etc. logs your sleep information of possibly you can export the data into your spreadsheet.

Sorry Dave, I didnt see your second option on the phone for some reason.
Let me have a look at it properly.

Cheers