> >
>

# excel time function

#1
09-28-09, 07:00 PM
Member
Join Date: Jul 2006
Location: Central MN
Posts: 556
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.

#2
09-29-09, 12:12 PM
Member
Join Date: Feb 2008
Location: WA
Posts: 149
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
10-05-09, 10:31 PM
Member
Join Date: Feb 2008
Posts: 188
Originally Posted by bclacquer
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

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
Pingbacks are Off
Refbacks are Off