Time in HH:MM:SS

[Support required please]

Hi
I’m struggling to solve my problem and hoping you can help. I’ve a column of names in one tab alongside a time (number format e.g 210) which can be given in seconds or hours but currently in minutes sitting in another column in the same tab associated with the respective name to the left, for example times in minutes to run the marathon. In another tab within the same workbook, again I have the same list of names but this time in a different order in one column, and I’m wanting to add the correct time alongside the correct name in a different column, but this time I need the time to be in time format HH:MM:SS. Hopefully this makes sense and someone may be able to help. Thank you

 

[MARK858]

If they are minutes then you just need to divide the number by 1440 and format the cell as whatever time you want.

Book1
	FI
3	210
4
5
6	03:30:00
l1

Cell Formulas
Range	Formula
FI6	FI6	=FI3/1440

 

[Support required please]

I’ve tried and failed to add the excel, and my photos are too large to add, even individually. I hope this helps:

In sheet 1: The raw data
A B
1 Name Time (unformatted given in mins)
2 Ann 2100
3 Bill 840
4 Carl 1470
5 Dave 1680

In sheet 2: I need the times formatted into HH:MM:SS without me having to do manually for each time as I’ve done below

A B
1 Name Time in HH:MM:SS
2 Carl 24:30:00
3 Ann 35:00:00
4 Dave 28:00:00
5 Bill 14:00:00

So I need the formulae to convert the values (to me these are times in minutes) in sheet 1 into the format required in sheet 2, and also to be placed in the correct place as above based on the names being in a different order in the column to the first sheet.

 

[etaf]

Book4
	A	B
1	Dave	28:00:00
2	Carl	24:30:00
3	Bill	14:00:00
4	Ann	35:00:00
Sheet1

Cell Formulas
Range	Formula
B1:B4	B1	=INDEX(Sheet2!$B$1:$B$4,MATCH(Sheet1!A1,Sheet2!$A$1:$A$4,0))/1440

format the cells as
[H]:MM:SS
note [] to go over 24 hours

edit sheets wrong way round
sorry
change sheet2!
to sheet1!

=INDEX(Sheet1!$B$1:$B$4,MATCH(A1,Sheet1!$A$1:$A$4,0))/1440

 

[Support required please]

Book4
	A	B
1	Dave	28:00:00
2	Carl	24:30:00
3	Bill	14:00:00
4	Ann	35:00:00
Sheet1

Cell Formulas
Range	Formula
B1:B4	B1	=INDEX(Sheet2!$B$1:$B$4,MATCH(Sheet1!A1,Sheet2!$A$1:$A$4,0))/1440

format the cells as
[H]:MM:SS
note [] to go over 24 hours

edit sheets wrong way round
sorry
change sheet2!
to sheet1!

=INDEX(Sheet1!$B$1:$B$4,MATCH(A1,Sheet1!$A$1:$A$4,0))/1440

That’s super, thank you. I think we managed it too based on your hard work.

We used on my spreadsheet in the cell range B2:B5 in sheet 2:

=vlookup(A1,Sheet 1!$A$2:$B$5,2,FALSE)/1440

We then formatted the cells in the cell range B2:B5 as you did above to get the relevant formatting for the time.

 

[etaf]

you are welcome