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

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

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

vb.net Code:

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.

Quote:

---

Originally Posted by anhn

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.

Quote:

---

Originally Posted by jmcilhinney

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.

Quote:

---

Originally Posted by 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

---

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??

Quote:

---

Originally Posted by jmcilhinney

vb.net Code:

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 :(

Quote:

---

Originally Posted by anhn

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 :(

Quote:

---

Originally Posted by snakegaer

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")

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.

Quote:

---

Originally Posted by anhn

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

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

---

awsom
thanx a lot :wave:

Quote:

---

Originally Posted by 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.

---

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

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

vb.net Code:

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.

Quote:

---

Originally Posted by snakegaer

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

---

www.msdn.com