Convert Downloaded Hours and Minutes from Date and Time Back To Hours

[OilEconomist]

Thanks in advance for any assistance provided by anyone.

Please excuse the title as it should state "Convert Downloaded Minutes and Seconds from Date and Time Back To Minutes and Seconds.

The solution to this can be VBA or just simple Excel.
The following is a sample set of data which I have downloaded (Column A) which is suppose to be minutes and seconds. Please note that the original data set is much larger than this or I would just manually make the change.

Book2
	A	B	C	D	E	F	G
1	DOWNLOADED DATA		MANUAL			FORMULA
2			MINUTES	SECONDS		MINUTES	SECONDS
3	19:28:00		19	28		19	7:28 PM
4	27:50:00		27	50		3	3:50 AM
5	19:48		19	48		19	7:48 PM
6	23:46		23	46		23	11:46 PM
7	25:19:00		25	19		1	1:19 AM
8	18:43		18	43		18	6:43 PM
9	3:50		3	50		3	3:50 AM
Sheet1

Cell Formulas
Range	Formula
F3:F9	F3	=HOUR(A3)
G3:G9	G3	=TIME(HOUR(A3), MINUTE(A3), SECOND(A3))

I need to extract the minutes into column C and the seconds in column D. For this small sample size I have manually done it (Columns C & D). The issue is that once the data is pasted, it is stored as a date and time format and I cannot figure out what formula to use to extract the minutes and seconds.

In column F I use an hour formula to extract the hours, but if it's greater than 24 minutes it only calculates everything after the 24 minutes (27 minutes shows as 3 minutes) (see Row 4 and Row 9 issue). In column G I was trying to use a formula for the minutes, but could not figure anything out to give me just minutes.

 

[AlphaFrog]

Minutes
=MINUTE(TIMEVALUE("00:"&LEFT(A3,5)))

Seconds
=SECOND(TIMEVALUE("00:"&LEFT(A3,5)))

 

[OilEconomist]

Minutes
=MINUTE(TIMEVALUE("00:"&LEFT(A3,5)))

Seconds
=SECOND(TIMEVALUE("00:"&LEFT(A3,5)))

Hi @AlphaFrog. It looks like the data did not copy over properly so that did not work. I tried again and it did not work. What was in my original post is what's visible to the human eye. When you view the contents of the cell, it looks as follows:

and this is what is in the actual cells of column A:

Book2
	A	B	C	D	E	F	G
1	DOWNLOADED DATA		MANUAL			FORMULA
2			MINUTES	SECONDS		MINUTES	SECONDS
3	1/1/1900 7:28:00 PM		19	28		19	7:28 PM
4	1/1/1900 3:50:00 AM		27	50		3	3:50 AM
5	7:48:00 PM		19	48		19	7:48 PM
6	11:46:00 PM		23	46		23	11:46 PM
7	1/1/1900 1:19:00 AM		25	19		1	1:19 AM
8	6:43:00 PM		18	43		18	6:43 PM
9	3:50:00 AM		3	50		3	3:50 AM
Sheet1

Cell Formulas
Range	Formula
F3:F9	F3	=HOUR(A3)
G3:G9	G3	=TIME(HOUR(A3), MINUTE(A3), SECOND(A3))

 

[bebo021999]

A4 = "01/01/1900 03:50:00" = 1d + 3h50' = 27h50' ==> /60 = 27m50s
A1 = "01/01/1900 19:28:00" = 1d + 19h28' = 43h28' ===>/60 = 43m28s
Why did you expect it to be 19h28' ??

Book1
	A	B	C	D	E	F	G
1	DOWNLOADED DATA		MANUAL			FORMULA
2			MINUTES	SECONDS		MINUTES	SECONDS
3	01/01/1900 19:28		19	28		43	28
4	01/01/1900 03:50		27	50		27	50
5	7:48:00 PM		19	48		19	48
6	11:46:00 PM		23	46		23	46
7	01/01/1900 01:19		25	19		25	19
8	6:43:00 PM		18	43		18	43
9	3:50:00 AM		3	50		3	50
Sheet2

Cell Formulas
Range	Formula
F3:F9	F3	=MINUTE($A3/60)
G3:G9	G3	=SECOND($A3/60)