how to convert this function from excel to vb???

[snakegaer]

hello

i have been looking around so long just to know the correct way to convert this function from excel to vb

=TEXT(A45/24,"hh:mm")

anyone can help???

this function convert the number to hours and minutes

thanks

[anhn]

VB classic :

Num = 8.5 '-- 8 and a half hours
x = Format(CDate(Num/24),"hh:nn") '-- 08:30

Same as the Excel formula above, this function only works correctly for Num >= 0 and Num < 24

Edit: CDate may not required: x = Format(Num/24,"hh:nn") '-- 08:30

[jmcilhinney]

Dim ts As TimeSpan = TimeSpan.FromDays(dayCount)
Dim str As String = String.Format("{0:D2}:{1:D2}", _
                                  CInt(Math.Floor(ts.TotalHours)), _
                                  ts.Minutes)
 
MessageBox.Show(str)

There are numerous ways to create a TimeSpan. If you don't have a number of days then check the documentation for the appropriate way given the data you have.

[jmcilhinney]

this function only works correctly for Num >= 0 and Num < 24

Which is why you should use a TimeSpan rather than a DateTime. A DateTime represents a moment in time, while a TimeSpan represents a time period.

[anhn]

Which is why you should use a TimeSpan rather than a DateTime. A DateTime represents a moment in time, while a TimeSpan represents a time period.

No argue, but the function I provided
x = Format(Num/24,"hh:nn")
is equivalent exactly with the original Excel formula:
=TEXT(A45/24,"hh:mm")
No more or less.

[snakegaer]

VB classic :

Num = 8.5 '-- 8 and a half hours
x = Format(CDate(Num/24),"hh:nn") '-- 08:30

Same as the Excel formula above, this function only works correctly for Num >= 0 and Num < 24

Edit: CDate may not required: x = Format(Num/24,"hh:nn") '-- 08:30

i'm useing vb.net 2003

the code is about the same i tried this one

Dim num As Decimal = 2.5
Dim x As Date
x = Format(System.DateTime.FromOADate(num / 24), "hh:mm")
y4.Text = x

but what the answer was is = 02:30:00 AM
i only need the hours and the mins how am i ganna remove the last part??

[snakegaer]

Dim ts As TimeSpan = TimeSpan.FromDays(dayCount)
Dim str As String = String.Format("{0:D2}:{1:D2}", _
                                  CInt(Math.Floor(ts.TotalHours)), _
                                  ts.Minutes)
 
MessageBox.Show(str)

There are numerous ways to create a TimeSpan. If you don't have a number of days then check the documentation for the appropriate way given the data you have.

i think thats far too complicated 4 me to understand sorry

[snakegaer]

No argue, but the function I provided
x = Format(Num/24,"hh:nn")
is equivalent exactly with the original Excel formula:
=TEXT(A45/24,"hh:mm")
No more or less.

yeah it works fine with vb6
but i'm usein vb.net 2003

[anhn]

i'm useing vb.net 2003

the code is about the same i tried this one

Dim num As Decimal = 2.5
Dim x As Date
x = Format(System.DateTime.FromOADate(num / 24), "hh:mm")
y4.Text = x

but what the answer was is = 02:30:00 AM
i only need the hours and the mins how am i ganna remove the last part??

The Format() function returns a String, not Date.

y4.Text = Format(System.DateTime.FromOADate(num / 24), "hh:mm")

[jmcilhinney]

It's pretty simple stuff really. Create a TimeSpan and then use it to create a formatted string containing hours and minutes. Every method in that code is in the MSDN Library and you can simply click each one and press F1 to go straight to the appropriate topic.

[snakegaer]

The Format() function returns a String, not Date.

y4.Text = Format(System.DateTime.FromOADate(num / 24), "hh:mm")

awsom
thanx a lot

[snakegaer]

It's pretty simple stuff really. Create a TimeSpan and then use it to create a formatted string containing hours and minutes. Every method in that code is in the MSDN Library and you can simply click each one and press F1 to go straight to the appropriate topic.

there is no msdn library in my cp i tried to install it but didn't work out

[jmcilhinney]

If you're going to go that way at least do it the .NET way:

y4.Text = Date.FromOADate(num / 24).ToString("HH:mm")

Note the upper case "HH" too, or else you'll get anything over 12 hours reported incrrectly.

[jmcilhinney]

there is no msdn library in my cp i tried to install it but didn't work out

www.msdn.com