PDA

View Full Version : Solved: Date and Time Difference 2



vzachin
08-27-2009, 07:08 PM
hi,

Paul & Deepak helped me last night

http://www.vbaexpress.com/forum/showthread.php?t=28186

i marked it solved too soon.

i have another question:

In columns G:I are formulas for DAYS,HOURS,MIN. I'm having a problem with the MINUTES

The formula for days is =INT(F4)
The formula for hour is = 24*ROUND((F4-INT(F4)),2)

The formula for minutes = 1440*(F4-INT(F4))

this formula calculates the TOTAL minutes past 60 minutes. if it's 60 minutes, then all i need to show is 1 Hour & 0 minutes

i modified Paul's code to include Round, but i cannot get the minutes to work
=INT(F8)&" days, "&ROUND((24*(F8-INT(F8))),0)&" hour(s),"&ROUND((1440*(F8-INT(F8))),2)&" mins"

please take a look

thanks again
zach

Bob Phillips
08-28-2009, 01:38 AM
How about?

=TEXT(B4-A4,"d ""day(s)"" h ""hours"" m ""mins"" s ""secs""")

vzachin
08-28-2009, 04:58 AM
hi Bob,

very nice!

any idea why these 2 formula i used didn't work?

= 1440*(F4-INT(F4))

- or -


=INT(F8)&" days, "&ROUND((24*(F8-INT(F8))),0)&" hour(s),"&ROUND((1440*(F8-INT(F8))),2)&" mins"

thanks
zach

Bob Phillips
08-28-2009, 05:16 AM
The first one can only ever give you a decimal count of the minutes.

The second uses the same logic to count the minutes, it doesn't limit it to 59.

vzachin
08-28-2009, 06:15 AM
bob,

thanks for the explanation

zach