Convert decimal to time

Hello All

I want to be able to grab a number, anything from 0.016666 to 86399.999999
and convert it to a time

For example:

0.50 = 30 seconds or 00:00:30 and 00:30
1.50 = 1 minute, 30 seconds or 00:01:30 and 01:30
197.50 = 3 hours, 17 minutes, 30 seconds or 03:17:30 and 197:30

I can do the calculations by hand (on a calculator) but can't get my head around the code or the formatting of the end figure.

Can someone please help

Dahquim
dahquimAsked:
Who is Participating?
 
mmusanteConnect With a Mentor Commented:
If the number N is expressed in minutes ...

format(N/1440,"hh:nn:ss")
0
 
dahquimAuthor Commented:
Thanks for the reply
This works great, i feel so dumb for not realising this.

However, how do i do the second part

so 03:17:30 = 197:30

Would it be just  case of taking the hours multipling by 60 and then adding the minutes?

Thanks for the help

Dahquim
0
 
dahquimAuthor Commented:
I have sorted it, thanks for the help.

Dahquim
0
 
mmusanteCommented:
Yas for Min:Secs format, to have back the minutes-with-decimals format try this ...

cdbl(#03:17:30#)*1440
0
 
Arthur_WoodCommented:
try this:

1) convert the number to Seconds

Dim Seconds As Long
Dim Number As Double

Number = Text1.Text

Seconds = Number * 60

2) then conver the number of seconds into Days, Hours, minutes and seconds in that order:
Dim Days As Long
Dim Hours As Long
Dim Minutes As Long

Days = Fix(Seconds / 86400) '86400 seconds in 1 day)
Hours = Fix((Seconds - Days * 86400) / 3600) ' 3600 Seconds in 1 Hour
Minutes = Fix((Seconds - Days * 86400 - Hours * 3600) / 60)
Seconds = Seconds - Days * 86400 - Hours * 3600 - Minutes * 60
Text2 = Days
Text3 = Hours
Text4 = Minutes
Text5 = Seconds


AW
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.