another excel time question

[snapper1969]

Hi,

Is it possible to take a time in a cell (00:02:30) for instance and change it to 2mins 30secs in a macro?

Also, is it possible to blank a cell where the time is 00:00:00?

Thx,

John

[PaulSR]

Hi John,

Is this what you're after? It changes the cell then returns it to the spreadsheet overwriting the 00:00:00 format version.

Hope it helps,

Paul.

Code: Select all

XLOpen>C:\Paul_PlayMacros\Time.xlsx,1,xlBook
XLGetSheetDims>xlBook,Sheet1,nRows,nCols
Let>Count=1

Repeat>Count
      XLGetCell>xlBook,Sheet1,%Count%,1,TimeValue
      VBEval>FormatDateTime(%TimeValue%,3),FormattedTime

      If>TimeValue=0
        Let>NewTime=
        Goto>SkipOver
      EndIf

      MidStr>FormattedTime,1,2,Hours
      MidStr>FormattedTime,4,2,Minutes
      MidStr>FormattedTime,7,2,Seconds

      Let>NewTime={(%Hours%+" Hours, "+%Minutes%+" Minutes, "+%Seconds%+" Seconds.")}
label>SkipOver
      XLSetCell>xlBook,Sheet1,%Count%,1,%NewTime%,outcome
      Let Count=Count+1
Until>Count>nRows

XLSave>xlBook,C:\Paul_PlayMacros\Time.xlsx
XLQuit>xlBook

[snapper1969]

Hi,

Thx for the reply, all I need is the macro to loop through D7:D30, would the code be different as I am getting a few errors at the moment?.

Thx,

John

[PaulSR]

Syntax error on the line that increments the count value - should've been

Let>Count=Count+1

Try this :

Code: Select all

//Play With Time
XLOpen>C:\Paul_PlayMacros\Time.xlsx,1,xlBook
Let>Count=7

Repeat>Count
      XLGetCell>xlBook,Sheet1,%Count%,4,TimeValue
      VBEval>FormatDateTime(%TimeValue%,3),FormattedTime

      If>TimeValue=0
        Let>NewTime=
        Goto>SkipOver
      Else
        MidStr>FormattedTime,1,2,Hours
        MidStr>FormattedTime,4,2,Minutes
        MidStr>FormattedTime,7,2,Seconds
        Let>NewTime={(%Hours%+" Hour(s), "+%Minutes%+" Minute(s), "+%Seconds%+" Second(s).")}
      EndIf
label>SkipOver
      XLSetCell>xlBook,Sheet1,%Count%,4,%NewTime%,outcome
      Let>Count=Count+1
Until>Count>30

XLSave>xlBook,C:\Paul_PlayMacros\Time.xlsx
XLQuit>xlBook

That'll do D7:D30 only.

[snapper1969]

Hi,

Thanks for the code. I am getting a type mismatch for 'FormatDateTime', any ideas?

Thx,

John

[PaulSR]

Hmmm not sure as it works fine for me.

Is the data in the cells D7:D30 formatted as per your original post? (00:00:00 time format)?

[KenHadley]

Hmmm not sure as it works fine for me.

Is the data in the cells D7:D30 formatted as per your original post? (00:00:00 time format)?

I am interested in the same question and tried out Paul's code. It is working fine for me, using the information on the data provided in the initial email.

Ken

[Marcus Tettmar]

Trailing blanks? Invisible control chars?

What happens if you try this:

XLGetCell>xlBook,Sheet1,%Count%,1,TimeValue
Trim>TimeValue,TimeValue
VBEval>FormatDateTime(%TimeValue%,3),FormattedTime

If it's not that it could be that TimeValue is not coming back as a DateTime value. Maybe a string which needs converting first.

Could try:

VBEval>FormatDateTime(Timevalue("%TimeValue%"),3),FormattedTime