Discussion Re: Problem with a Excel formula in Excel
https://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/101078#M10490
<P>Hi Steven,</P><P> </P><P>Thank you, it's clear now. First comment - why do you use outdated format for Excel file (.xls)? I attached both, try one with .xlsx.</P><P> </P><P>Second - your initial array formula</P><PRE>=INDEX($B1:$B309,MAX(IF((E1:E309>0),ROW(E1:E309),0)))+ROUND(E316,0)</PRE><P>works fine. Most probably you didn't enter it as an array formula.</P><P> </P><P>To do that</P><P>1) Double click on cell with your formula</P><P>2) Press and hold CTRL and SHIFT simultaneously</P><P>3) Press ENTER</P><P>4) Release all keys</P><P> </P><P>Curly brackets shall appears at the beginning and end of formula.</P><P> </P><P>Your formula could be bit simplified</P><PRE>=MAX((E1:E309>0)*$B1:$B309)+ROUND(E316,0)</PRE><P>above is also array formula. Here you find maximum value in B1:B309 filter it by column E (all rows with any positive numbers).</P><P> </P><P>Not to use array formulas another couple of variants could be</P><PRE>=MAX(INDEX((E1:E309>0)*$B1:$B309,))+ROUND(E316,0)</PRE><P>that's similar to previous one, however no array formula is required. INDEX returns an array with all dates in B1:B309 for which there is any positive number in next column, otherwise zero. We take maximum date in that array.</P><P> </P><P>One more variant for not array formula</P><PRE>=LOOKUP(2,1/(E1:E309>0),$B1:$B309)+ROUND(E316,0)</PRE><P>Second parameter returns a vector with 1 or errors as results of dividing the 1 on TRUE and FALSE. We lookup 2 in this vector (actually any number greater than 1), since no such number in the vector, the position of latest 1 will be returned. Finally the date in B1:B309 at this position.</P><P> </P><P>In attached files all above formulas are sequentially in E325, E326, E327, E328</P><P> </P>Sun, 27 Aug 2017 13:02:57 GMTSergei Baklan2017-08-27T13:02:57ZProblem with a Excel formula
https://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/98123#M10477
<P>Hi Not sure what I've done but the only thing I've changed is the number of cells/ lines in the range from: =MAX(IF('Draw Results'!C1:C2248={3,4},'Draw Results'!D1:D2248))</P><P>to =MAX(IF('Draw Results'!C1:C2283={3,4},'Draw Results'!D1:D2283))</P><P>What it does is to show the last draw number entered in in column A where either 3 or 4 are entered in column D. The answer should be 2257 but it comes out as "0"</P><P> </P><P>The second formula was changed</P><P>from: =MAX(IF('Draw Results'!C1:C2248={3,4},'Draw Results'!A1:A2248))</P><P>to =MAX(IF('Draw Results'!C1:C2283={3,4},'Draw Results'!A1:A2283))</P><P>What it should show is the date of the last draw entered in column A, where 3 or 4 is entered in column D. the answer should be 08/07/17 but comes out as 00/01/00.</P><P> </P><P>Each formula is entered with ctrl+shift+ebter</P><P>Tried other excell help sites but got no help so please don't let me down</P>Wed, 25 Jul 2018 16:56:29 GMThttps://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/98123#M10477steven Green2018-07-25T16:56:29ZRe: Problem with a Excel formula
https://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/98138#M10478
<P>Hi Steven</P><P> </P><P>Please re-check your updated formula are array ones</P>Mon, 21 Aug 2017 22:15:20 GMThttps://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/98138#M10478Sergei Baklan2017-08-21T22:15:20ZRe: Problem with a Excel formula
https://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/98191#M10479
Hi Sergie. How do I do that?Tue, 22 Aug 2017 04:53:09 GMThttps://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/98191#M10479steven Green2017-08-22T04:53:09ZRe: Problem with a Excel formula
https://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/98235#M10480
<P>Hi Steven,</P><P> </P><P>That's just an assumptions - your formula always returns zero, other words always returns FALSE, thus most probably doesn't work on entire range, that could means it's entered not as array formula.</P><P> </P><P>Stay on the cell with your formula, shift into cell edit mode (F2 or double-click) and Ctrl+Shift+Enter then. {} shall appear.</P><P> </P><P>To check how it works in ribbon Formulas -> Evaluate formula and follow the steps. But better to shorten your range for such tests.</P><P> </P>Tue, 22 Aug 2017 08:49:12 GMThttps://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/98235#M10480Sergei Baklan2017-08-22T08:49:12ZRe: Problem with a Excel formula
https://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/99697#M10481
<P>Hello again. ive tried that and even shortened the range but it is still the same. Not sure what iam looking at when i do the evaluate thing.</P>Tue, 22 Aug 2017 21:28:50 GMThttps://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/99697#M10481steven Green2017-08-22T21:28:50ZRe: Problem with a Excel formula
https://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/99716#M10482
<P>Steven,</P><P> </P><P>Do i understood correctly the only thing you did is change 2248 on 2283 in your formulas? And perhaps that's misprint in your description</P><P> </P><BLOCKQUOTE><HR /><P>to =MAX(IF('Draw Results'!C1:C2283={3,4},'Draw Results'!D1:D2283))</P><P>What it does is to show the last draw number entered in in column A where either 3 or 4 are entered in column D. The answer should be 2257 but it comes out as "0"</P><P> </P></BLOCKQUOTE><P>And i guess it's misprint in your description - 3 or 4 are entered in column C, not D. Number is returned from column D, not A. At least that's what your formula do - returns max in D for the rows where 3 or 4 in C.</P><P> </P><P>Anyway, zero means formula doesn't find 3 or 4. Another reason could be cells in C are formatted as text and you have text "3" instead of number 3. You may check, for example, by ISNUMBER on the cell with this value.</P><P> </P><P>To shorten the range for the test you may take C2256:C2258 (instead of C1:C2283), same for other ranges. Evaluate shall show something like</P><PRE>MAX(IF({FALSE,FALSE;TRUE,FALSE;FALSE,FALSE},D2258:D2258))</PRE><P>on second step. If in C2257 is number 3.</P><P> </P><P> </P>Tue, 22 Aug 2017 22:05:28 GMThttps://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/99716#M10482Sergei Baklan2017-08-22T22:05:28ZRe: Problem with a Excel formula
https://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/100181#M10483
<P>Hi Sergei.</P><P>Sorry i took so long to reply but ive been working hard. Thank you for your help with that formula but i have manged to work around the problem using a different set of formulas.</P><P> </P><P>How ever i do have a problem with another formula on the sheet (note for some reason formulas with the same long cell ranges all stopped working when they reached 2248)</P><P> </P><P>First ill explain what the formula does. The sheet is a Lotto program that uses information on the sheet to predict a date when the number in question is likly to come out again, based on when it was last drawn and the average number of days between draws for each number (dont laugh i know this is impossible to be 100% correct due to random chance).</P><P>(note; the number of rows has been reduced)</P><P>The formula in cell E317 is =INDEX($B1:$B309,MAX(IF((E1:E309>0),ROW(E1:E309),0)))+ROUND(E316,0)</P><P>Colum B holds the date of each draw.</P><P>Colum E represents the number 1. (numbers 1- 59 are in coloums E to BK) Each time number a is drawn then and numer 1 (when drawn as a main ball) or number 2 (when drawn as a bonus ball) is entered in the relevent colum next to the date it was drawn.</P><P>Cell E316 contains the day average (this is the day total from the the first to the last draw entered devided by the number of times drawn).</P><P>The problem is that when i use ctrl+shift+enter the formula return #VALUE in cell 317.</P><P> </P><P>Any questuion please let me know.</P><P> </P><P>Steven</P>Wed, 23 Aug 2017 22:49:15 GMThttps://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/100181#M10483steven Green2017-08-23T22:49:15ZRe: Problem with a Excel formula
https://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/100348#M10484
<P>Hi Steven,</P><P> </P><P>In general your formula shall work. You may use equivalent which doesn't require array formula</P><PRE>=INDEX($B1:$B309,MATCH(MAX(E1:E309),E1:E309,0)))+ROUND(E316,0)</PRE><P> </P>Thu, 24 Aug 2017 12:52:25 GMThttps://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/100348#M10484Sergei Baklan2017-08-24T12:52:25ZRe: Problem with a Excel formula
https://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/100412#M10485
<P>hI Sergei. i have imput the formula you sent. If you have a look at the attachment youll see what it coming up with. Thank you for your help.</P>Thu, 24 Aug 2017 15:14:37 GMThttps://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/100412#M10485steven Green2017-08-24T15:14:37ZRe: Problem with a Excel formula
https://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/100522#M10486
<P>Sorry, it was misprint</P><PRE>=INDEX($B1:$B309,MATCH(MAX(E1:E309),E1:E309,0))+ROUND(E316,0)</PRE>Thu, 24 Aug 2017 17:01:50 GMThttps://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/100522#M10486Sergei Baklan2017-08-24T17:01:50ZRe: Problem with a Excel formula
https://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/100663#M10487
Hi again. The formula seems to be working but the date that it gives is in the past. It should show the next date that number might be drawn. Any suggestions?Thu, 24 Aug 2017 22:35:45 GMThttps://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/100663#M10487steven Green2017-08-24T22:35:45ZRe: Problem with a Excel formula
https://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/100881#M10488
<P>Steven,</P><P> </P><P>Could you give the sample file with the data up to 10 records with the comment on it what shall be received?</P>Fri, 25 Aug 2017 18:06:42 GMThttps://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/100881#M10488Sergei Baklan2017-08-25T18:06:42ZRe: Problem with a Excel formula
https://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/101057#M10489
<P>Hi Sergei. Ive attached a copy of the sheet have a look please the formula you sent is on line 317.</P>Sun, 27 Aug 2017 08:14:06 GMThttps://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/101057#M10489steven Green2017-08-27T08:14:06ZRe: Problem with a Excel formula
https://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/101078#M10490
<P>Hi Steven,</P><P> </P><P>Thank you, it's clear now. First comment - why do you use outdated format for Excel file (.xls)? I attached both, try one with .xlsx.</P><P> </P><P>Second - your initial array formula</P><PRE>=INDEX($B1:$B309,MAX(IF((E1:E309>0),ROW(E1:E309),0)))+ROUND(E316,0)</PRE><P>works fine. Most probably you didn't enter it as an array formula.</P><P> </P><P>To do that</P><P>1) Double click on cell with your formula</P><P>2) Press and hold CTRL and SHIFT simultaneously</P><P>3) Press ENTER</P><P>4) Release all keys</P><P> </P><P>Curly brackets shall appears at the beginning and end of formula.</P><P> </P><P>Your formula could be bit simplified</P><PRE>=MAX((E1:E309>0)*$B1:$B309)+ROUND(E316,0)</PRE><P>above is also array formula. Here you find maximum value in B1:B309 filter it by column E (all rows with any positive numbers).</P><P> </P><P>Not to use array formulas another couple of variants could be</P><PRE>=MAX(INDEX((E1:E309>0)*$B1:$B309,))+ROUND(E316,0)</PRE><P>that's similar to previous one, however no array formula is required. INDEX returns an array with all dates in B1:B309 for which there is any positive number in next column, otherwise zero. We take maximum date in that array.</P><P> </P><P>One more variant for not array formula</P><PRE>=LOOKUP(2,1/(E1:E309>0),$B1:$B309)+ROUND(E316,0)</PRE><P>Second parameter returns a vector with 1 or errors as results of dividing the 1 on TRUE and FALSE. We lookup 2 in this vector (actually any number greater than 1), since no such number in the vector, the position of latest 1 will be returned. Finally the date in B1:B309 at this position.</P><P> </P><P>In attached files all above formulas are sequentially in E325, E326, E327, E328</P><P> </P>Sun, 27 Aug 2017 13:02:57 GMThttps://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/101078#M10490Sergei Baklan2017-08-27T13:02:57ZRe: Problem with a Excel formula
https://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/101197#M10491
<P>Thank you again. i will try them out when i can.</P>Mon, 28 Aug 2017 09:46:53 GMThttps://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/101197#M10491steven Green2017-08-28T09:46:53ZRe: Problem with a Excel formula
https://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/101199#M10492
HI Again could I save a file created in .xls format as a .xlsx file.Mon, 28 Aug 2017 09:53:45 GMThttps://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/101199#M10492steven Green2017-08-28T09:53:45ZRe: Problem with a Excel formula
https://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/101211#M10493
<P>Hi Steven,</P><P> </P><P>Yes, sure - if your Excel version is 2007 or any later</P>Mon, 28 Aug 2017 10:50:10 GMThttps://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/101211#M10493Sergei Baklan2017-08-28T10:50:10ZRe: Problem with a Excel formula
https://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/102313#M10494
<P>Hi Sergei.</P><P> Thank you for your help on this. I am not sure what or if iam doing anything wrong but the original formula still doesn't work even though i've shortened the range and i am using ctrl+shift+enter. The second formula works ok but when i add another 1 or 2 in the colloum it stops working and shows #VALUE in the cell. This is the same with the other formulas.</P><P> </P><P>Iam going out on a limb and iam sending you the full sheet, almost as i use it, as i am beginning to think that the shhet may be iver complicated and this might be the problem. Let me know what you think.</P>Thu, 31 Aug 2017 10:41:10 GMThttps://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/102313#M10494steven Green2017-08-31T10:41:10ZRe: Problem with a Excel formula
https://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/102332#M10495
<P>Hi Steven,</P><P> </P><P>Strange. I have no problems with your file, no #VALUE in the cells. Both for array formulas (row 322) and lookup formula (row 317).</P><P> </P><P>Could you please indicate in which exactly cell you add 1 (e.g. F248) and in which cell #VALUE appears (e.g. F317) after that.</P><P> </P><P>And on which version of Excel you are? </P>Thu, 31 Aug 2017 12:51:06 GMThttps://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/102332#M10495Sergei Baklan2017-08-31T12:51:06ZRe: Problem with a Excel formula
https://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/103056#M10496
<P>Hi Sergei. #VALUE appears in the cell E317. not sure what is going on, weather it me or some problem with the program iam not sure. </P>Sun, 03 Sep 2017 22:00:48 GMThttps://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/103056#M10496steven Green2017-09-03T22:00:48ZRe: Problem with a Excel formula
https://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/103136#M10497
<P>Hi Steven,</P><P> </P><P>I can't reproduce that. Whatever i enter in cells from E1 to E247 i have no error in E317.</P><P> </P><P>Is file you attached with previous post with error? And please, what's your Excel verson?</P>Mon, 04 Sep 2017 09:23:50 GMThttps://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/103136#M10497Sergei Baklan2017-09-04T09:23:50ZRe: Problem with a Excel formula
https://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/104909#M10498
<P>Hello again. sorry it took so long to reply. My last message i said about it not working ok. SORRY it seems its my fault. i had not put the formula in right. It seems ok but ill let you know. thank you for your help.</P>Sun, 10 Sep 2017 14:15:31 GMThttps://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/104909#M10498steven Green2017-09-10T14:15:31ZRe: Problem with a Excel formula
https://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/104936#M10499
<P>Hi Again. seems i've not got it figured out. I have attached the sheet. As you can see ive put the draw results in row 249. and in row 317 under the draw results then #VALUE appears in the that cell. not sure whay but could you please let me know what ive done wrong. Thank you</P>Sun, 10 Sep 2017 20:56:39 GMThttps://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/104936#M10499steven Green2017-09-10T20:56:39ZRe: Problem with a Excel formula
https://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/105588#M10500
<P>Hi Steven,</P><P> </P><P>Sorry, missed this your reply. I tried to play with the file you send a week or so ago and was not able to reproduce the error. But that was no data in row 249.</P><P> </P><P>Could you please send me the file where you see the error?</P>Tue, 12 Sep 2017 15:59:08 GMThttps://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/105588#M10500Sergei Baklan2017-09-12T15:59:08ZRe: Problem with a Excel formula
https://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/109018#M10501
<P>Hello Again. Ive attached the sheet. wot ive done is and your formula to the cells in row 317. Ive added the a new set of draw results in to row 249. If you look at cells K317, R317 etc you'll see #VALUE</P>Thu, 21 Sep 2017 20:49:28 GMThttps://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/109018#M10501steven Green2017-09-21T20:49:28ZRe: Problem with a Excel formula
https://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/109244#M10502
<P>Hi Steven,</P><P> </P><P>Unfortunately in the file you attached i don't see the formulas (only #REF) since 'General Lotto Info' tab is removed. Could you please send full file to check?</P><P> </P><P>If you care about the confidentiality send the link where i can download it with in-private message.</P>Fri, 22 Sep 2017 11:52:35 GMThttps://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/109244#M10502Sergei Baklan2017-09-22T11:52:35ZRe: Problem with a Excel formula
https://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/111287#M10503
<P>Hi Sorry about that. i had to delete some of the none essential page and deleted the wrong one. Ill try again. Please try this one.</P>Wed, 27 Sep 2017 20:55:39 GMThttps://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/111287#M10503steven Green2017-09-27T20:55:39ZRe: Problem with a Excel formula
https://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/111463#M10504
<P>Hi Steven,</P><P> </P><P>That's since in B249 you enter the text "12.7.17" instead of the date in your locale format. If change on date (actually number) formulas work. Please see attached.</P>Thu, 28 Sep 2017 10:54:33 GMThttps://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/111463#M10504Sergei Baklan2017-09-28T10:54:33ZRe: Problem with a Excel formula
https://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/112427#M10506
<P>Hi Sergei. Ive done as you have suggested but still getting the same probelm. Ive attached a PDF of a screen shot of what i get when i try to open the file to work on it. I have circled the cells that have #Value in them incase the image is not great. </P>Sun, 01 Oct 2017 20:48:15 GMThttps://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/112427#M10506steven Green2017-10-01T20:48:15ZRe: Problem with a Excel formula
https://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/112567#M10507
<P>Hi Steven,</P><P> </P><P>As the date you have to enter the date in your locale format (12/07/2017 by default for UK) or simply the number 42928 (which is equivalent of that date). Or by refrence on previous date</P><PRE>=B248+4</PRE><P>And only after that apply format if necessary.</P><P> </P><P>Please see the file in my previous post.</P>Mon, 02 Oct 2017 13:43:19 GMThttps://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/112567#M10507Sergei Baklan2017-10-02T13:43:19ZRe: Problem with a Excel formula
https://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/116506#M10508
<P>Hi Sorry it took so long to reply but my laptop has been away for repair. Will try your suggestion as sson as i can. thank you</P>Fri, 13 Oct 2017 20:13:32 GMThttps://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/116506#M10508steven Green2017-10-13T20:13:32ZRe: Problem with a Excel formula
https://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/121986#M10509
Hi service. Tried all you said and looked at the file you attached but still won't work.Sun, 29 Oct 2017 21:33:52 GMThttps://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/121986#M10509steven Green2017-10-29T21:33:52ZRe: Problem with a Excel formula
https://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/124288#M10510
<P>Steven,</P><P> </P><P>That could be something else, but i see no errors in my copy of the file. How your date is entered you may check in formula bar. If as text</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="DateText.JPG" style="width: 213px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/23555i12A5A13898228498/image-size/large?v=1.0&px=999" title="DateText.JPG" alt="DateText.JPG" /></span></P><P>you have an error, if as date</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="DateAsDate.JPG" style="width: 160px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/23556i298FE6B56ADF492C/image-size/large?v=1.0&px=999" title="DateAsDate.JPG" alt="DateAsDate.JPG" /></span></P><P>all works</P>Sun, 05 Nov 2017 08:23:40 GMThttps://techcommunity.microsoft.com/t5/excel/problem-with-a-excel-formula/m-p/124288#M10510Sergei Baklan2017-11-05T08:23:40Z