Computers - excel time function

Doityourself.com community forum was created to provide answers to all questions related to home improvement and home repair. Doityourself community can help you find information about how-to topics on small fixes to large remodeling projects. With comprehensive how-to content and expertly moderated community forums DoItYourself.com makes it easy to tackle even the most complex home improvement projects.



PDA

View Full Version : excel time function

bclacquer
09-28-09, 07:00 PM
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.

txrxio
09-29-09, 12:12 PM
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
rbwest
10-05-09, 10:31 PM
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