With monday.comâ€™s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

If I have a table containing 1 field and these are time values ie 10:00:00 and 20:00:00 I want to be able to sum the field and be returned with the answer 30:00:00 however when I sum the fields I get the answer 1.25. I then use the format command in the following way.

format([expr1],"hh:nn:ss")

I would expect the answer 30:00:00 but I get 06:00:00, hence it loses the 24:00:00 is there a format I can use to show time values greater than 24:00:00??

format([expr1],"hh:nn:ss")

I would expect the answer 30:00:00 but I get 06:00:00, hence it loses the 24:00:00 is there a format I can use to show time values greater than 24:00:00??

Thus EVERY time value is measuerd in Days and fractions of a day - thus 30 hours = 1 day + 6 hours or 1.25 days (which is exactly the value that you are getting)

You can convert that 1.25 to total hours, minutes and seconds by converting the Date variable to a Type Double, and then multiplying by 24 (# of hours per day), and then, in code, converting the reulting NUMBER back to the equivalent Hours, minutes and seconds.

AW

Isn't this what I've tried to do by using the line:

Format([expr1],"hh:nn:ss")

But although the number is 1.25 I still get the answer 06:00:00.

Carl.

The format function does not convert 1 day to 24 hours and add that to the hours part of the time.

AW

convert the answer to a string - thus you get "6:00:00"

take the first number before the ":" val(left(cstr([Time]),inst

take the integer of the time, so 1.25 would give you 1, then multiply this by 24 and add to the 6 giving 30.

a bit long winded and if anybody comes up with a better solution then i'm all ears. heres the solution in full:

CStr(Val(Left(CStr(Format(

Hope this helps

Select sum(fldTime) as SumofTimes from TimesTable;

However, if the sum exceeds 24 hours you will get a date time string.

Select Sum(fldTime ) * 1 as SumofTimes from TimesTable; This will yield days and fractions of days.

Working down the pieces:

Select Int(Sum(fldTime ) * 1) as Days, CDate(Sum(fldTime) * 1 - Days)) as Frac,

Days * 24 + format(Frac,"hh")) as Hours, (CStr(Hours) & format$(Frac,":nn:ss")) as DHMS from Table;

AW

Been away dealing with life - but I'm all better now <G> Thanks for the welcome, glad to be back AW!

Select Int(Sum(fldTime) * 1) as Days, CDate(Sum(fldTime) * 1 - Days) as Frac,

Days * 24 + format(Frac,"hh") as Hours, (CStr(Hours) & format$(Frac,":nn:ss")) as DHMS from dailyappts;

Select Format$(Fix([fldTime]*24),

Originally & :" didn't cut it without the other double quote. I also found that by leading the nn:ss string with a colon produce the correct result.

He deserves the points.

Select Format$(Fix([fldTime]*24),

Should be:

Select Format$(Fix(Sum([fldTime])

First you want the sum, and then you want to multiply the integer part by 24, not the whole sum.

All Courses

From novice to tech pro — start learning today.

When you sum a date/time field, you do actually get a numeric value, but Access automatically displays this as a date/time - although I missed out a ", it should read:

Format$(Fix([expr1]*24),"0