time foramt issue

[robcarr]

Hi,

in E2 i have 1390700

in E1 i have =E2/86400 formatted as [h]:mm:ss

e2 displays - 386:18:20

how can i get the displayed value in VBA

i have tried formatting the value in vba

by using

u = Format(range("e2").value / 86400, "[h]:mm:ss")

i get ":01:20"

how can i u to show 386:18:20

i dont need it in a time format as the value is only being put into an email, for a visual affect only.

Hope this is of use, Rob.

 

[TonyJollans]

Try ..

[blue][tt] Range("E1").[/tt][red][tt]Text[/tt][/red][/blue]

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[combo]

If you are in vba/excel:
Application.WorksheetFunction.Text(1390700/86400, "[h]:mm:ss")

In pure vba there is no such format, you have to combine strings (Int(1390700/86400), Format(1390700/86400,":mm:ss")).

combo

 

[robcarr]

thanks for the responses

i use

ua = Application.WorksheetFunction.Text(u / 86400, "[h]:mm:ss")

this to get the proper value for the email, however they want to know how many fte made up the hours, so I would have to divide total(386:18:20) by 7.5hrs (standard day)
to get a value

when i use

ub = (Int(u / 86400) & Format(u / 86400, "mm:ss"))

it comes up with 16:01:20

any ideas

Hope this is of use, Rob.

 

[combo]

Sorry for some mess. First, to get hours the time interval in seconds should be divided by 3600. Secondly, the vb(a) minute format is 'n'. So should be:
Int(1390700/3600) & Format(1390700/86400,":nn:ss")

combo

 

[xlbo]

...and Tony's suggestion: Range("E1").Text ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[robcarr]

yes that was good as well, just wanted to use the other way

Hope this is of use, Rob.