Displaying A Duration in Time on a graph with general number formatted axis

[andyb123]

Hi,

I'm looking for some help please.

I need to find a way of including a value that is being displayed as a duration (e.g. 12:32 as mm:ss) but is inserted into excel using the decimal number system (as it allows me to add different times together).

Generally these values that I am working with all lie between 3mins and 35mins (03:00 and 35:00).

I already have a graph where one axis uses this scale, so it would be a huge help if I could use this graph to also display my duration information.

Is there any way to do this? Currently the data is on the graph but is down the bottom as the actual decimal value that excel reads is something like 0.001232.



Thank you for your help.

 

[Yongle]

For info you can input times in as normal times (in example below 4:6:7 and 0:25) and add them together with custom format as illustrated below

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

86​

[/td][td]FORMAT[/td][td]Duration1[/td][td]Duration2[/td][td]SUM[/td][td]FORMULA[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

87​

[/td][td] [h]:mm:ss[/td][td]

4:06:07​

[/td][td]

0:25:00​

[/td][td]

4:31:07​

[/td][td] =SUM(B87:C87)[/td][td]hours minutes and seconds[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

88​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

89​

[/td][td] [m]:ss[/td][td]

246:07​

[/td][td]

25:00​

[/td][td]

271:07​

[/td][td] =SUM(B89:C89)[/td][td]minutes and seconds[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet 5[/td][/tr][/table]

For graphing
- convert the values (in another range) and graph those values

To convert durations into hours
- multiply by 24
- format the cell as a number

To convert durations into minutes
- multiply by 24 X 60
- format the cell as a number

To convert durations into seconds
- multiply by 24 X 60 X 60
- format the cell as a number

 

[andyb123]

Brilliant, that has worked! Thank you!!