Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Excel 2000: Time totaling variable 'overflows' at 24:00 and display

  Asked By: Hayed    Date: Jan 09    Category: MS Office    Views: 898
  

I have a time totaling variable (declared as type Date) which I use
to implement a running total of time (hours and minutes).

I put a Watch on the variable to see what happens as the total moves
towards 24:00, and lo and behold, when the total gets to over
23:59:59 it goes into a mixed date + time format with the time being
truncated to a remainder (modulus) of 24. The date shown is 31-Dec-
1899.

When I assign from the Date type variable into a String type
variable, I get exactly that date in the string, i.e., with the "31-
Dec-1899" hh:mm:ss.

Is there any way to fix this? Like applying a mask/format to the Date
variable? Or a masked/formatted conversion to the string?

Or will I have to convert to integers and manage a structure with two
components { Hours as Integer, Minutes As Integer }? Ugggghhhh!!!

Or keep the total as a floating point number?

Share: 

 

4 Answers Found

 
Answer #1    Answered By: Ellen Simpson     Answered On: Jan 09


I think you need to use the format string "[h]:mm"

 
Answer #2    Answered By: Patricia Johnson     Answered On: Jan 09

I thought I did.

To what should I apply the format string?

 
Answer #3    Answered By: Calandre Bernard     Answered On: Jan 09

The square brackets are a part of the format.

It's a custom format for the cell you want to display  the result in.

 
Answer #4    Answered By: Alyssa Campbell     Answered On: Jan 09

Here is a link to a site which gives excel  formulas for working with
time and date variables. I have used some of the formulas in VBA and
they worked great.

http://www.cpearson.com/excel/datearith.htm

 




Tagged: