LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 386
Default Adding Times

Real amateurs question -

I have a list of times formatted as hh:mm which i need to sum. If sum of
the values is less than 1 day the function is sum(a1:a20), however if the
total exceeds 24 hours the function needs to be sum(a1:a20)*24.

I need a function that knows whether to use the standard sum or the x24
version, however a standard if statement doesn't work as the standard sum
equation will always return a value less than 24 hours. One further
complication is the formatting - in order to get a value greater than 24
hours to display the true value it needs fomatted as a general number,
however values less than 24 hours need the hh:mm format.

Example

1:00
8:00
5:00
6:00
9:00

sum(a1:a4) = 20:00
if i ask it to sum(a1:a5) it will return 05:00 (the hours leftover from one
full day)
if i ask it to sum(a1:a5)*24 and format it as a number it returns 29.

Anyone any idea of function that can do all of this decision making by itself?
so if ask excel
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Adding Times mudge55 Excel Discussion (Misc queries) 3 May 8th 08 09:49 PM
adding times together changetires Excel Discussion (Misc queries) 4 June 30th 06 01:40 PM
adding times Metalteck Excel Discussion (Misc queries) 1 September 2nd 05 08:48 PM
Adding up Times Lisa Excel Worksheet Functions 2 September 1st 05 02:32 PM
ADDING TIMES John C. Harris, MPA Excel Worksheet Functions 5 August 9th 05 05:32 PM


All times are GMT +1. The time now is 04:50 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"