VB convert total sum of days to datetime

Question

I have an Excel sheet that i'm trying to transcribe to a VB form in order to get equipment's up time from an input number.

Let's say that the calculation results in, ex: 4.391286731 that is the days total.

In Excel I applied:

=INT(col+row) &" Days " & HOUR(col+row) & " h " & MINUTE(col+row) & " m " & SECOND(col+row) & " s".

The return is 4 Days 9h 23m 27s perfect.

Now in VB, after getting the final calculation , ex: the same 4.391286731 how can I convert this total to a date/time format similar to the Excel one above?

Possible duplicate of [Get years, months, days for an elapsed span of time (DateTime)](http://stackoverflow.com/questions/27902973/get-years-months-days-for-an-elapsed-span-of-time-datetime) — Ňɏssa Pøngjǣrdenlarp, Jan 07 '16 at 19:37
assuming that by "VB" you really mean VB.NET (as per the tag) and not excel-vba/vba/vbscript or some other variant — Ňɏssa Pøngjǣrdenlarp, Jan 07 '16 at 19:40
Just a FYI You could use this in Excel instead, `=TEXT(A1,"d ""Days"" h""h"" m""m"" s""s""")` for a shorter formula. — Scott Craner, Jan 07 '16 at 19:47
@ScottCraner - I find escaping the text with backslashes makes for a cleaner looking format mask. — , Jan 07 '16 at 19:54
@Jeeped something new for me learn. :) `=TEXT(A1,"d \D\a\y\s h\h m\m s\s")` — Scott Craner, Jan 07 '16 at 19:56
This will also works for me in vb.net `TimeSpan.FromDays(4.391286731).ToString("d\ \d\a\y\s\ h\h\ m\m\ s\s")` — Scott Craner, Jan 07 '16 at 20:30

Dave 5709 · Answer 1 · 2016-01-07T19:54:14.833

The data type you're looking for is TimeSpan.

Convert Decimal days to a TimeSpan:

Dim t As TimeSpan = TimeSpan.FromDays(4.391286731)

New TimeSpan from days, minutes, ETC:

dim t as new TimeSpan([Days], Hours, Minutes, Seconds)

Edit: Timespan.ToString() will give you a string representation, but not in your requested format.

string.format("{0:%d} Days {0:%h}h, {0:%m}m, {0:%s}s", timespan.fromdays(4.391286731))

Returns: "4 Days 9h, 23m, 27s"

MSDN Custom TimeSpan Format Strings

score 0 · Answer 2 · 2016-01-07T19:53:40.337

0

Use the Format command.

From the Immediate window with 4.391286731 in the ActiveCell.

?format(int(activecell.Value2), "0 \d\a\y\s ")&format(activecell.Value2, "h\h m\m s\s")
4 days 9h 23m 27s

You cannot mix-and-match a 0 format mask with h, m or s but you can certainly concatenate two Format strings together.

edited Jan 07 '16 at 19:53

answered Jan 07 '16 at 19:48

VB convert total sum of days to datetime

2 Answers2