Copy contents of a cell containg time/date 1

[paulbasi]

I’m trying to dump a text file from an Excel sheet with calculated results.
I need to do some time calculations and then take the results and export them to a text file.

Here is a simplified example of what I am trying to do …

A1 = start time
B1 = duration
C1= end Time (calculated)

D1 formula will read
=CONCATENATE (“Start time is “,A1,” for a duration of “,B1,” ending at “,C1)

I have formatted C1 to use time hh:mm
C1 displays exactly the way I want it to in the sheet but if I try to copy the result from C1, it will read in a decimal form. So instead of the result being:
Start time is 1:00 for a duration of 30 ending at 1:30
It reads
Start time is 1:30 for a duration of 30 ending at 0.56251530


Thank you

 

[SkipVought]

Hi,

Use the TEXT function...
[tt]
=CONCATENATE (“Start time is “,Text(A1,"hh:mm"),” for a duration of “,Text(B1,"hh:mm"),” ending at “,Text(C1,"hh:mm"))
[/tt]


Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]

 

[AnotherHiggins]

See faq68-5827 for info on how Excel deals with dates and times to understand why 1:30 is stored as 0.56251530.

My first thought is if you are creating a text file, why not just make whatever changes you want in Excel and Save As a .csv or .txt right there.

As for your question - you can change your formula to return a text value that looks like the time.

In C1, you'd have something like:
[tab]=Text(A1 + B1, "hh:mm")


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[paulbasi]

Thank you ... exactly what the doctor ordered!