Post #185,702
12/1/04 11:45:04 AM
|
Help with Excel
I have two Excel cells containing Login date and time (Column D) and Logout
date and time (Column E). The format of each is MM/DD/YYYY HH:MM:SS xM(AM PM). I
would like to subtract the time in D from the time in E.
I tried to do a straight subtract (@sum(En-Dn))but didn't get the desired result.
Can anyone suggest the correct formula?
Thanks
Al Vitale
|
Post #185,703
12/1/04 11:48:34 AM
|
Maybe use DATEVALUE and TIMEVALUE?
|
Post #185,722
12/1/04 2:27:45 PM
|
Re: Help with Excel
I did a little experiment with 2 cells formatted for date and time using the type listed (in O2003) as 3/14/01 13:30 in other words date with 'military' time
I put 12/1/04 12:20 in a1 and 12/1/04 13:10 in b1
I formatted cell c1 for time using the type listed as 13:30:55
the formula in c1: =b1-a1 gave a result of 0:50:00
A
Play I Some Music w/ Papa Andy
Saturday 8 PM - 11 PM ET
All Night Rewind 11 PM - 5 PM
Reggae, African and Caribbean Music
[link|http://wxxe.org|Tune In]
|
Post #185,726
12/1/04 2:38:13 PM
|
Add days for when logout is next day.
A good friend will come and bail you out of jail ... but, a true friend will be sitting next to you saying, "Damn...that was fun!"
|
Post #185,745
12/1/04 4:53:40 PM
|
Re: Add days for when logout is next day.
with answer cell formatted general
you could use the int function
with
a1 12/1/04 12:20
b1 1/4/05 13:10
formula
=int((b1-a1)*24)
would give you 816 (hours)
if you needed more accuracy you could use
=((b1-a1)*1440)/60
which gives 816.8333
plain =b1-a1
gives a result of 34.03472222 (days)
A
Play I Some Music w/ Papa Andy
Saturday 8 PM - 11 PM ET
All Night Rewind 11 PM - 5 PM
Reggae, African and Caribbean Music
[link|http://wxxe.org|Tune In]
|
Post #185,847
12/2/04 2:22:27 PM
|
Re: Help with Excel
Andrea,
Thank You
Al Vitale
|
Post #185,849
12/2/04 3:00:10 PM
|
Andy doesn't like that. ;-)
|
Post #185,852
12/2/04 4:02:44 PM
|
Re: Andy doesn't like that. ;-)
Anything that includes 'thank you' works for me
A
Play I Some Music w/ Papa Andy
Saturday 8 PM - 11 PM ET
All Night Rewind 11 PM - 5 PM
Reggae, African and Caribbean Music
[link|http://wxxe.org|Tune In]
|
Post #185,868
12/2/04 6:44:37 PM
|
Thank you, Andrea Dear, I'll remember that! ;^>
|
Post #185,900
12/3/04 7:59:22 AM
12/3/04 11:58:08 AM
|
You're Welcome Charlene
Play I Some Music w/ Papa Andy
Saturday 8 PM - 11 PM ET
All Night Rewind 11 PM - 5 PM
Reggae, African and Caribbean Music
[link|http://wxxe.org|Tune In]
Edited by andread
Dec. 3, 2004, 11:58:08 AM EST
|
Post #185,901
12/3/04 8:02:03 AM
|
His welcome charlene what?
Thangyew, thangyew. I'll be here all week.
Peter
[link|http://www.debian.org|Shill For Hire]
[link|http://www.kuro5hin.org|There is no K5 Cabal]
[link|http://guildenstern.dyndns.org|Home]
|
Post #185,903
12/3/04 8:13:39 AM
|
TGIF!
;-)
I'll be away until Wednesday. Hold down the fort, eh?
Cheers,
Scott.
|
Post #185,947
12/3/04 2:13:44 PM
|
Re: You're Welcome COMMA Charlene
C-, see me.
Peter
[link|http://www.debian.org|Shill For Hire]
[link|http://www.kuro5hin.org|There is no K5 Cabal]
[link|http://guildenstern.dyndns.org|Home]
|
Post #185,948
12/3/04 3:02:34 PM
|
C-h k C-,
C-, is undefined
--
This guy's ahead of his time! He's using quantum programming methods: in universes where invalid data is passed to this function, it does not return. Thus you are ensured that you will only have valid data after calling it. Optimally you'd destroy the universe on failure, but computers haven't quite advanced to that level yet.
-- [link|http://thedailywtf.com/archive/2004/10/26/2920.aspx|The] Daily WTF
|
Post #185,725
12/1/04 2:37:11 PM
|
What version Excel?
In 2002 I just subtract Login from Logout (En-Dn) and then format the output cell with a custom format d h:mm:ss - gives you DAYS HH:MM:SS
Should work.
A good friend will come and bail you out of jail ... but, a true friend will be sitting next to you saying, "Damn...that was fun!"
|
