Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2010 If Then logic - with time
I would like to use a time format as the logical test and the statement does not seem to recognize the time format. It converts it to some 1.XXXX number.
Any suggestions? Thanx, Richard |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2010 If Then logic - with time
I would like to use a time format as the logical
test and the statement does not seem to recognize the time format. It converts it to some 1.XXXX number. Any suggestions? If you're trying to test the value of a cell containing a time, remember that a time is really a number: a fraction of a day. For example, 0.5 is noon, or 12 hours (12:00) -- half a day. So compare times like numbers. If you're trying to test if a cell is in the time format or not, take a look at the function CELL(info_type, [reference]) with info_type="format". (I have Excel 2010.) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2010 If Then logic - with time
"MyVeryOwnSelf" wrote:
I would like to use a time format as the logical test and the statement does not seem to recognize the time format. [....] If you're trying to test if a cell is in the time format or not, take a look at the function CELL(info_type, [reference]) with info_type="format". Caveat lector: CELL("format",A1) recognizes only some time formats. For example, for hh:mm:ss, it returns "D8". But it returns "G" for [h]:mm:ss, a common alternative. Moreover, CELL is not recalculated if you only change the referenced cell's format. So you must recalculate the cell that uses CELL manually, in one of several ways. IMHO, it is "bad practice" to use the cell's format as a formula condition. But viable alternatives can only be suggested if/when the "rmscg15" explains why he/she wants to rely on the cell format in the first place. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Having difficulty with Microsoft date & time picker in excel 2010 | Setting up and Configuration of Excel | |||
Run time error specifying range for sort code vba excel 2010 | Excel Programming | |||
Correction: Excel 2010 Chart Time Format | Charts and Charting in Excel | |||
how can i get the date & time format 2010/02/17 12:05 in excel | Excel Discussion (Misc queries) | |||
Form 4 Range of time from 1/20/2010 4:00 AM To 1/21/2010 10:00 AM | Excel Worksheet Functions |