Macro to export to Excel: duration types

[Brian Bonner]

I have a macro to export the duration to Excel.
1. in Project it shows the proper min., hours, days etc type.
2. but what is exported is the time in minutes.
3. I figured out how to get it in hours, days, etc but dividing by the
proper multiple of 60.
4. What I haven't figured out is how to concatenate the value with the
appropriate type in the Excel cell.
5. for example - it will export 53. How do I get 53 mins in the cell?

 

[DavidC]

Hi Brian,

If I understand your question correctly, you are looking for help on this
matter once the data is in Excel.

It seems that you can get the right number of minutes, hours or days as
appropriate by having excel do the necessary calculation, but you now want to
have the units for the time showing as well. If this is right, then try this
(note this is really for the Excel board not Project)

In the column adjacent to the converted duration type in "=If( cell
reference<60,"min","hours")
This will set the units to either mins or hours depending on the value in
the adjacent cell.
In the next column type in the adjacent cell, "=Concatenate(cell reference1,
" " , cell reference2)"

If you have durations that you want in days then the if statement needs to
be expanded to include durations greater than 7 days being weeks.

Hope this is of some help.

Regards

Davidc

 

[Brian Bonner]

That seems doable but a lot of macro steps to do in my .mpp macro. What I was
hoping for was a VB form of the DurationFormat or Concatenate command that I
could stick in the .mpp macro.

This is what my .mpp macro looks like now...

Set c = s.Range("G2")
RowIndex = 0
For j = 1 To I - 1
c.offset(RowIndex, 0).Value = Duration(j)
If Duration(j) > 60 Then
If Duration(j) > 1439 Then
c.offset(RowIndex, 0).Value = (Round((Duration(j) / 1440), 2))
Else
c.offset(RowIndex, 0).Value = (Duration(j) / 60)
End If
End If
RowIndex = RowIndex + 1
Next j

I thought I might be able to add something like:
c.offset(RowIndex, 0).Value = (Duration(j) / 60)
c.offset(RowIndex,0).Value=Concatenate((Duration(j) / 60) + "hrs")

 

[DavidC]

Hi Brian,

Sorry I missed the point about doing the conversion within the export macro.
Without checking your proposal, it seems like the approach I would take
anyway. My suggestion is to give it a go and debug from there.

Good luck

DavidC

 

[John]

That seems doable but a lot of macro steps to do in my .mpp macro. What I was
hoping for was a VB form of the DurationFormat or Concatenate command that I
could stick in the .mpp macro.

This is what my .mpp macro looks like now...

Set c = s.Range("G2")
RowIndex = 0
For j = 1 To I - 1
c.offset(RowIndex, 0).Value = Duration(j)
If Duration(j) > 60 Then
If Duration(j) > 1439 Then
c.offset(RowIndex, 0).Value = (Round((Duration(j) / 1440), 2))
Else
c.offset(RowIndex, 0).Value = (Duration(j) / 60)
End If
End If
RowIndex = RowIndex + 1
Next j

I thought I might be able to add something like:
c.offset(RowIndex, 0).Value = (Duration(j) / 60)
c.offset(RowIndex,0).Value=Concatenate((Duration(j) / 60) + "hrs")

Brian,
From you code it looks like you have gathered the duration values into
an array of "I" elements. As far as I know there is no "concatenate"
function (as you show in your second statement to be added). Perhaps you
are just expressing your intent in pseudo code.

Here is what I would do. First I would create a small sub procedure or
function that takes the duration value and breaks it down into days,
hours, and minutes. Kind of like what you have in your "If" statements.
I notice you use a factor of 1440 which I assume is to convert to days
(24 hours), but remember, in Project duration is in working time which
means there are only 480 minutes per day.

As far as creating the concatenated string, use something like this:
Cstr(Duration(j)/60) & "hours"
Cstr(Round(Duration(j)/480)) & "days"
I always use the ampersand but you can also use the "+".

You may also need to set the cell format in Excel for Text but I don't
remember if that is necessary.

Hope this helps.
John
Project MVP

 

[JD]

If you want to format the data before you push it to excel you can use the
integer division and mod functions along with some ampersands for
concatenation. Personally, I'd rather not track things to the minute, but
that is just because I think there are too many minutes to bother counting.

in this example tMins = time in minutes

excelblahblah.value = tMins\60 & " hours, " & tMins Mod 60 & " minutes"

The ampersand performs concatenation of text strings. You can put whatever
you want within the quotation marks.

For more about the integer division function see my article about it here:
http://zo-d.com/blog/archives/programming/vba-integer-division-and-mod.html

-Jack Dahlgren

 

[Brian Bonner]

Thank you Jack - exactly what I needed.

I used:
If Duration(j) > 60 Then
If Duration(j) > 1439 Then
c.offset(RowIndex, 0).Value = (Round((Duration(j) / 1440), 2)) &
" days"
Else
c.offset(RowIndex, 0).Value = Duration(j) / 60 & " hrs"
End If
End If

 

[JackD]

Brian, why not simply?:

c.offset(RowIndex, 0).Value = Duration(j) \ 1440 & " days " & (Duration(j)
Mod 1440)/60 & " hours"

integer division and mod should be your friend here.

-Jack Dahlgren