Time

[maxflia10]

What are the consequences of using a custom format of [mm] and b1-a1 versus b1-a1*24*60

 

[Mark W.]

On 2002-10-16 11:23, maxflia10 wrote:
What are the consequences of using a custom format of [mm] and b1-a1 versus b1-a1*24*60

The [mm] format translates an internal date/time value into minutes... leaving the underlying value as is -- expressed in terms of days. =(B1-A1)*24*60 or =(B1-A1)*1440 produces a value in terms of minutes.

 

[maxflia10]

Thanks Mark,

 

[maxflia10]

OK now I'm trying to get rid of 0's without using the Tools/Options and custom formatting. I have in a cell this formula

=IF(COUNT(E7,G7),SUM(AH7-AG7)*1440,"")

However when time is entered only in E7 I get the ### error. How can I construct this formula so that both e7 and g7 have times, until then the cell with the formula is blank?

 

[Mark W.]

=IF(COUNT(E7,G7)=2,(AH7-AG7)*1440,"")

...however, it's unclear what's the relationship among your columns E:E, G:G, AH:AH, and AG:AG.
This message was edited by Mark W. on 2002-10-16 12:48

 

[maxflia10]

Mark,

What I'm attempting is custom formatting columns E and with 00:00, then in columns AH and AG I have this

=(LEFT(REPT("0",4-LEN(E6))&E6,2)&":"&RIGHT(E6,2))+0

Trying not to type in the ":", the users of this spreadsheet had requested this.

Thanks for the help!

 

[Mark W.]

You could also use this array formula...

{=SUM(MID(TEXT(E6,"0000"),{1,3},2)/{24,1440})}

Note: This is an array formula which must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula. For more on array formulas see the Excel Help topic for "About array formulas and how to enter them".

 

[maxflia10]

Mark

Thanks!