Decimal to hours and minutes conversion

[MarkRoberts]

Ok....so...here's my problem.

I have created a multi sheet workbook with a consolidation sheet at the end. On the consolidation sheet I am using the concatenate function to see if a cell is populated on one of the other 11 sheets. If it is, I would like it to display the contents of the relevant cell in the consolidation sheet cell.

The problem I am having is that the original cell contains a time in hours and minutes. However, when it appears on the consolidation sheet it is converted to a decimal. I would like this to be displayed in the same time format as the previous sheets but no matter which conversion formula I use, it doesn't appear to display correctly.

Can anyone help?

Regards,

Mark

[shg]

Welcome to the forum, Mark.

I think we'd be hard-pressed to help without seeing what you're doing.

Maybe it's as simple as formatting the result as Time?

[MarkRoberts]

Hi shg,

I'm actually at home now and don't have the file on my home PC. I will get someone at the office to email it over to me in the morning and will post the formula I'm using.

I don't think it is as simple as formatting as Time as my cells are already formatted to hours and minutes, but you never know.

I will get back to you as soon as I have the file.

Thanks

Mark

[DonkeyOte]

If you're concatenating the 11 sheet values then the chances are the resulting value is a number stored as text.
Time is Decimal so the result you're seeing is seemingly the time value of interest but as outlined it's being stored as a string hence not displaying as time.
To show this value as time you need to "coerce" your formula output back to a number, eg:

=0+(yourformula)

If the cell is formatted as time that should work...

However it's worth noting that if:

a) the cell of interest is the same on each of the 11 sheets
b) the cell never contains error values
c) the 11 sheets are listed in sequence

you could perhaps just use a 3D Sum as opposed to concatenation ?

In syntax terms:

=SUM('First Sheet:Last Sheet'!CellReference)