Working with dates, times, hours & min.

[EdNerd]

Transcribing time data into a worksheet & having trouble getting the formats and values I need for formulas.

I have a start date and time and end date and time. All four values are in different (but sequential) columns. I need to use these values to get the total time lapsed (the time extends overnight, so the end date is always greater and the time is always less).

Then I have four columns of blocks of time (hours & min, though it may be 0 hrs); these need to be added to verify they make up the entire time block, and each block needs to be calculated as a percentage of the whole.

Any help is greatly appreciated!

 

[maabadi]

Please upload Example file & Desired Results with XL2BB ADDIN (Preferable) OR upload at free hosting site e.g. www.dropbox.com, GoogleDrive or OneDrive then Input link here.
For Cumulative hours Use custom format and type: [h]:mm

 

[EdNerd]

Awesome add-in!!! I want to be that smart some day!

Here is the sheet I am currently using. These are times for total and stages of sleep. The date is the wake-up date, so it pairs with the AM time; the date for the PM time is always a day earlier.
The end goal is to have actual times for all the totals and the stages, as well as percentages of a whole, that I can look at on a chart.

Theresa Fitbit Sleep.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Date (Woke up)	Start	Stop	Score	Awake	REM	Light	Deep	Total	Awake	REM	Light	Deep	Total
2	1/3/2021	9:42 PM	8:25 AM	86	1.15	2.36	5.47	1.5	9.28	1hr, 15mn	2hr, 36mn	5hr, 47mn	1hr, 5mn	9hr, 28mn
3	1/2/2021	10:11 PM	8:28 AM	87	1.16	2.32	4.36	1.53	9.1	1hr, 16mn	2hr, 32mn	4hr, 36mn	1hr, 53mn	9hr, 1mn
4	1/1/2021	12:43 AM	9:25 AM	75	1.28	2.24	3.7	1.43	7.14	1hr, 28mn	2hr, 24mn	3hr, 7mn	1hr, 43mn	7hr, 14mn
Sheet1

Cell Formulas
Range	Formula
J2:N4	J2	=CONCATENATE((LEFT(E2,1)),"hr, ",(RIGHT(E2,IF(LEN(E2)=3,1,2))),"mn")

 

[Dave Patton]

Your data is not clear. If you enter the information as time, the example shown below may help.

Date and Time 2021.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Date	Start	Stop	Time	Score	Awake	REM	Light	Deep	Awake	REM	Light	Deep
2	1-Jan-21	21:42	08:25	10:43		0:30	2:36	5:47	1:50	4.7%	24.3%	54.0%	17.1%
3	2-Jan-21	22:11	08:28	10:17		1:16	2:32	4:36	1:53	12.3%	24.6%	44.7%	18.3%
1a

Cell Formulas
Range	Formula
J2:M3	J2	=F2/$D2
D2:D3	D2	=C2-B2+(B2>C2)

 

[EdNerd]

For some reason, I thought you could only subtract times that covered more than one day if the value included the date: mm/dd/yy hh:mm. So one stumper was how to pull the Col A and Col B values together (in a formula or in another cell) to create that value. That doesn't have to be done?

I don't understand your formula in D2: =C2-B2+(B2>C2) ?? What does the (B2>C2) expression do? I've never seen it used like that.

 

[Dave Patton]

Did you try the formula?

What does the (B2>C2) expression do?
If the time spans midnight (times like 10:00 PM to 7:00 Am), the expression is True which evaluates to 1 and the calculation yields the correct time.
see example below and an alternative
Edit The formulas that I posted also convert the time to decimal. instead of 7:30 it would show 7.5

T201810c.xlsm
	A	B	C
1	22:00	6:00	8
2	22:00	6:00	8
3d

Cell Formulas
Range	Formula
C1	C1	=MOD(B1-A1,1)*24
C2	C2	=(B2-A2+(A2>B2))*24

 

[EdNerd]

Yes, I did try it, and saw that it worked. It just seemed unnatural to add a "greater than" operation. But if it's TRUE, then you're adding 1, which gives the correct answer. Like I said, I never saw it used like that before.

Thank you for all your help on this!!
Ed

 

[Dave Patton]

Thanks for the feedback.