Autosum

  • Thread starter Thread starter Gav
  • Start date Start date
G

Gav

Hi all,

Does anybody know why autosum or sum in general doesn't work on fields that
are formatted as dd:hh:mm:ss?

Thanks
Gav
 
my mistake the cells where typed in as dd:hh:mm:ss like it had been saved as
a csv file. Is there a way to convert it back to a DateTime field so that
calculations can be made on the field?
 
One way:

Say the times are in column A. In column B enter

B1: =LEFT(A1,FIND(":",A1)-1)+TIMEVALUE(MID(A1,4,8))

Which produces a number that can be summed.

Note: that isn't a date/time field, as you requested - in order to have
a date, you'll need more than just the day number (dd).

Note that while you can use the format dd hh:mm:ss, the dd that is
returned is the *day number*, not the number of days, so while
1.085462963 will display as 01 02:03:04 in the 1900 date system, it's
because 1 is evaluated as the day number of the date that occurs 1 day
after 31 December 1899. In the 1904 date system 1.085462963 will display
as 02 02:03:04, since 1 day after 1 January 1900 is 2 January.

Likewise a total greater than 31 (1900 date system) or 30 (1904 date
system) will roll over to the day number of the date in subsequent
months, so 32.085462963 will also dispaly as 01 02:03:04 (1900) or 02
02:03:04 (1904).
 
Back
Top