| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using the Time function with an IF statement
HI - I was hoping someone could help me with a time function.
=IF(W2=$B$2,IF($Z$2TIME(11,30,0),'Current Day Balances'!$B$4-'Current Day Balances'!$D$4,'Current Day Balances'!$F$4-'Current Day Balances'!$D$4),0) W2 is a date and B2 is today's date. Cell Z2 holds the time in military time. If it is today before 1:30pm, I would like it to subtract two cells. If it is today after 1:30pm, I want it to subtract two different cells. And if it isn't today, to equal zero. For some reason, I am getting True statement no matter what time it is. Can someone help me? Thanks. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using the Time function with an IF statement
Hi Allison,
Am Fri, 27 May 2016 13:31:07 -0700 (PDT) schrieb Allison Mazurek: =IF(W2=$B$2,IF($Z$2TIME(11,30,0),'Current Day Balances'!$B$4-'Current Day Balances'!$D$4,'Current Day Balances'!$F$4-'Current Day Balances'!$D$4),0) try: =IF(W2<$B$2,0,IF($Z$2TIME(11,30,0),'Current Day Balances'!$B$4-'Current Day Balances'!$D$4,'Current Day Balances'!$F$4-'Current Day Balances'!$D$4)) Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using the Time function with an IF statement
Unfortunately, it did not. It keeps picking up the first value statment.
=IF(V2<$B$2,0,IF($Z$2TIME(11,30,0),'Current Day Balances'!$B$4-'Current Day Balances'!$D$4,'Current Day Balances'!$F$4-'Current Day Balances'!$D$4)) On Friday, May 27, 2016 at 3:49:27 PM UTC-5, Claus Busch wrote: Hi Allison, Am Fri, 27 May 2016 13:31:07 -0700 (PDT) schrieb Allison Mazurek: =IF(W2=$B$2,IF($Z$2TIME(11,30,0),'Current Day Balances'!$B$4-'Current Day Balances'!$D$4,'Current Day Balances'!$F$4-'Current Day Balances'!$D$4),0) try: =IF(W2<$B$2,0,IF($Z$2TIME(11,30,0),'Current Day Balances'!$B$4-'Current Day Balances'!$D$4,'Current Day Balances'!$F$4-'Current Day Balances'!$D$4)) Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using the Time function with an IF statement
Hi Allison,
Am Fri, 27 May 2016 15:23:54 -0700 (PDT) schrieb Allison Mazurek: Unfortunately, it did not. It keeps picking up the first value statment. =IF(V2<$B$2,0,IF($Z$2TIME(11,30,0),'Current Day Balances'!$B$4-'Current Day Balances'!$D$4,'Current Day Balances'!$F$4-'Current Day Balances'!$D$4)) sorry, I forgot that in Z2 is a military time without colon. You have to convert it in a regular time. Try: =IF(V2<$B$2,0,IF(TIME(LEFT($Z$2,2),RIGHT($Z$2,2), 0)TIME(11,30,0),'Current Day Balances'!B4-'Current Day Balances'!D4,'Current Day Balances'!F4-'Current Day Balances'!D4)) Regards Claus B. -- Windows10 Office 2016 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using the Time function with an IF statement
It still is answering as TRUE every time even if I change the time in the formula.
On Friday, May 27, 2016 at 5:39:34 PM UTC-5, Claus Busch wrote: Hi Allison, Am Fri, 27 May 2016 15:23:54 -0700 (PDT) schrieb Allison Mazurek: Unfortunately, it did not. It keeps picking up the first value statment. =IF(V2<$B$2,0,IF($Z$2TIME(11,30,0),'Current Day Balances'!$B$4-'Current Day Balances'!$D$4,'Current Day Balances'!$F$4-'Current Day Balances'!$D$4)) sorry, I forgot that in Z2 is a military time without colon. You have to convert it in a regular time. Try: =IF(V2<$B$2,0,IF(TIME(LEFT($Z$2,2),RIGHT($Z$2,2), 0)TIME(11,30,0),'Current Day Balances'!B4-'Current Day Balances'!D4,'Current Day Balances'!F4-'Current Day Balances'!D4)) Regards Claus B. -- Windows10 Office 2016 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using the Time function with an IF statement
Hi Allison,
Am Tue, 31 May 2016 09:58:28 -0700 (PDT) schrieb Allison Mazurek: It still is answering as TRUE every time even if I change the time in the formula. please upload your workbook and post the link here. Regards Claus B. -- Windows10 Office 2016 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| If statement to compare time cell to a time | Excel Worksheet Functions | |||
| IF function returns function statement rather than result | Excel Worksheet Functions | |||
| using if statement to subtract 24 hours from time still shows as a negative time from both responses under 24 hours | Excel Worksheet Functions | |||
| if then statement = time | Excel Worksheet Functions | |||
| IF statement to calculate time usage in specific time bands | Excel Worksheet Functions | |||
Linear Mode
