excel time function

Reply

  #1  
Old 09-28-09, 07:00 PM
Member
Thread Starter
Join Date: Jul 2006
Location: Central MN
Posts: 556
Received 0 Votes on 0 Posts
excel time function

I am trying to figure out how to subtract 30min form a time. I have a "start" time of 7:00am and an "end" time of 3:30. I know how to find the elapsed time (3:30m - 7:00am = 8:30) but how do I subtract the :30 off of that answer without adjusting the times?

Ok I did (3:30 - 7:00)-.0202 why did this give me 8hrs? it is what I wanted but now I want to know why it worked.
 
Sponsored Links
  #2  
Old 09-29-09, 12:12 PM
Member
Join Date: Feb 2008
Location: WA
Posts: 149
Received 0 Votes on 0 Posts
Times and dates in Excel are stored in a particular fashion that is treated like a number, but looks nothing like a time or date. 0.0202 must be the equivalent of 30 minutes for that format
 
  #3  
Old 10-05-09, 10:31 PM
Member
Join Date: Feb 2008
Location: Canada
Posts: 188
Received 0 Votes on 0 Posts
Originally Posted by bclacquer View Post
I am trying to figure out how to subtract 30min form a time. I have a "start" time of 7:00am and an "end" time of 3:30. I know how to find the elapsed time (3:30m - 7:00am = 8:30) but how do I subtract the :30 off of that answer without adjusting the times?

Ok I did (3:30 - 7:00)-.0202 why did this give me 8hrs? it is what I wanted but now I want to know why it worked.
Here's another way to subtract the 30 sec off the answer using VBA in Excel.

Public Sub timeDiff()
' set variables
Dim startTime, endTime, lunchTime

' set start, end , and lunchtime variables with 24 hr time using TimeSerial function.
startTime = TimeSerial(7, 0, 0)
endTime = TimeSerial(15, 30, 0)
lunchTime = TimeSerial(0, 30, 0)

' show results using long time format command
MsgBox (FormatDateTime((endTime - startTime) - lunchTime, vbLongTime))

' The answer will be "8:00:00 AM" in a message box

End Sub
 
Reply

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Thread Tools
Search this Thread
Display Modes
 
Ask a Question
Question Title:
Description:
Your question will be posted in: