UDF that returns a date

Hi all,

I have a c# udf accessed as an automation add-in that needs to return dates 
to Excel. I know I can't manipulate the cell's NumberFormat from within the 
UDF, but is there a way to return a value that will be formatted in Excel as 
a date? It seems any string value I return will default to general. Let me 
know if any other info would help.


1/17/2008, 11:31:01 PM

The UDF should return a number to the cell, which can be formatted as
a date to suit your requirements. The number is an integer measuring
the number of elapsed days, with 1st Jan 1900 =3D Day 1. Excel assumes
(incorrectly) that 1900 was a leap year, so you need to take this into
account. The serial value for today's date (17th Jan 2008) is 39464.

Hope this helps.


On Jan 17, 11:31=A0pm, ep <e...@discussions.microsoft.com> wrote:
> Hi all,
> I have a c# udf accessed as an automation add-in that needs to return date=
> to Excel. I know I can't manipulate the cell's NumberFormat from within th=
> UDF, but is there a way to return a value that will be formatted in Excel =
> a date? It seems any string value I return will default to general. Let me=

> know if any other info would help.
> thanks

1/17/2008, 11:53:05 PM
Roedd ep <e**************microsoft.com> wedi ysgrifennu:

> Hi all,
> I have a c# udf accessed as an automation add-in that needs to return
> dates to Excel. I know I can't manipulate the cell's NumberFormat
> from within the UDF, but is there a way to return a value that will
> be formatted in Excel as a date? It seems any string value I return
> will default to general. Let me know if any other info would help.
> thanks


Here's a reply of mine (including original typos) to a similar question on a
different list. The proposed workaround is in native VBA, but I'm sure you
can translate into C# with a it's fun an games with delegates.


This idea comes up from time to time and its origins are in the way that
excel responds to the user entering a worksheet function such as NOW(). Try
it. Excel not only returns the current date and time, but also reformats the
cell. This leads some people to think that a) the formattring is being done
by the function and b) it might be possible to do something similar with a
UDF. In fact, the function is not doing the formatting. Excel is registering
that the function has been entered and is responding afterwards by
'helpfully' changing the number format. An internal list, to which we have
no access, is maintained of the functions which Excel thinks might benefit
from such reformatting.

Maybe this can be done with a global sheet change event hook. We need to
check if any cell changing was unformatted ("General") before we entered our
UDF. If it is, we change the formatting to however we want it. If the UDF is
in an add-in, the whole thing can be encapsulated within the add-in

Say we have a function called MyNow in a regular module in an Add-In:

Option Explicit

Function MyNow()
    MyNow = Now()
End Function

Now (ahem!) we put the following event code in the add-in's Thisworkbook

Option Explicit

Private WithEvents oApp As Excel.Application

Private Sub Workbook_Open()
    Set oApp = Application
End Sub

Private Sub oApp_SheetChange(ByVal Sh As Object, _
                ByVal Target As Range)
    If Target.NumberFormat = "General" And _
        UCase(Target.Formula) Like "=MYNOW(*)" Then _
            Target.NumberFormat = "mm:ss"
End Sub

This should do what we want subject to further testing (which I'm not going
to do since I don't have any use for this!). 

Robert Bruce
1/18/2008, 2:29:08 PM
Pretty clever, thanks Rob. 
1/18/2008, 3:36:02 PM
Also, any idea if there is native support (rather than catching events) for 
this in office 2007, or will there be in future editions? Thanks again.

1/18/2008, 5:42:02 PM
Yn newyddion: 2*************************0353BCEE5B@microsoft.com,
Roedd ep <e**************microsoft.com> wedi ysgrifennu:

> Also, any idea if there is native support (rather than catching
> events) for this in office 2007, or will there be in future editions?
> Thanks again.

Dunno mate. I'm not downgrading to 2007. I'll upgrade when they fix the 
broken UI.


Robert Bruce
1/19/2008, 10:24:47 AM

Thanks all, sanity returns !!
0 replies , 1/30/2004, 9:55:12 PM
Thank you all for the help with the "IF" formula. Incredible speed of the replies was amazing Cheers --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.561 / Virus Database: 353 - Release Date: 13/01/2004

Return 1st, 2nd, 3rd largest test values
8 replies , 3/21/2008, 7:33:05 PM
I am trying to write a formula that will look at a column of data and return the 1st, 2nd, and 3rd largest values. My major issue is that the values are text. Example: Term Reasons Failed Testing Failed Testing Failed Tesing Fingerprinting Failed BG Check Failed BG Check I need to return the data as... #1 Term Reason = Failed Testing #2 Term Reason = Failed BG Check #3 Term Reason = Fingerprinting Using Excel 2003 Thanks!

Return number of current sheet
3 replies , 4/25/2005, 2:43:03 PM
I'm looking for a way to create a function to return the current sheet you’re working in. Not it’s name (I’ve found that function), but the number of the sheet. Example: if you have a total of 8 sheets, and you’re working on the 3rd sheet, I’d like to return the number 3 in a certain cell on the third sheet, 4 on the fourth sheet, 5 on the fifth sheet, etc., and have those numbers update if sheets are added or deleted. Is that possible?

Return value
4 replies , 3/8/2006, 7:44:22 PM
I am working on a sheet with the following: Sheet 1 Col A = Description Sheet 1 Col B = ID Sheet 1 Col C = Returned value from Sheet 2 Col B when value in Sheet 1 Col B matches value in Sheet 2 Col A I would like to take a cell from Sheet 1 Col B and find the matching value in Sheet 2 Col A and fill in Sheet 1 Col C with associated value from Sheet 2 Col B. I know this is a simple lookup but I can't get it to work. -- DebP ------------------------------------------------------------------------ DebP's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=32273 View this thread: http://www.excelforum.com/showthread.php?threadid=520262

conditional formula: return 1 of 4 values
3 replies , 1/15/2006, 5:04:01 PM
How can i use a conditional formula to return one of 4 values, if C:13 is less than 274 should be 8, if C:13 is less than 329 should be 16, if C:13 is more than 329 should be 24, if C:13 is more than 438 should be 32. I know I'm close tough not quite =(C13<274)*8 + (C13>274=16)*(C13<329=24)*24 + (C13>438)*32

In worksheet 1, column E I have 1 Bedroom 2 Bedroom 2 Bedroom 3 Bedroom 1 Bedroom Studio In worksheet 2 (called "DROPDOWNS"), column D & E respectively I have: 1 Bedroom 1B 2 Bedroom 2B 3 Bedroom 3B Studio ST I want to do a vlookup in worksheet 1, colum D that will lookup the values in worksheet 2 and return the values from column E. I used the following formula : =VLOOKUP(E8,'DROP DOWNS'!D2:E11,2,FALSE) The formula works great until it fnds that it has already looked up the same value and the second time around it returns an N/A. How can I make the formula ignore other previously found values and just return the exact value from worksheet 2. Thanks!

sum returns #N/A
3 replies , 6/29/2006, 6:48:02 PM
I am getting a return of #N/A when I try to sum a column. Its a column of formulas which sometimes return #N/A also. How do I get around this problem and sum the values I do have? Thanks. Todd

Search for & return list of values
12 replies , 3/26/2007, 6:19:34 PM
I have a sheet with (6) columns and (306) rows. I would like to search for empty cells in column (6) and return the values in columns {2, 3, 4 & 5}. And continue until the last row 308 is processed. For Example: G307 is empty returns B307 2643F C307 pg 2 of 3 D307 Orange E307 Friday = 2643F pg 2 of 3 Orange Friday The empty Cell is G308 B308 2643F C308 pg 3 of 3 D308 Orange E308 Friday = 2643F pg 3 of 3 Orange Friday Is there a non VBE/VBA solution? TIA, Phil

Seems there is not a basic return address label template for avery 8160 for Office 2007. Does anyone know if there is one available and where I can download it?

Find Largest and Return Column Name
2 replies , 3/17/2006, 4:48:18 PM
I am comparing 3 columns and trying to find the maximum value of the columns. But instead of just displaying the maximum value I would like it to show which column the value is from. OBS_ID CLASS1 CLASS2 CLASS3 A 2 5 10 B 12 10 4 C 5 1 15 D 2 6 12 E 1 23 9 So in this case, when the formula is run it will return "CLASS3" for Row A and "CLASS1" for Row B and so on.. Thanks for you help

In an situtation where data is exported from Access that has fields with hard returns (carriage returns)when Excel stores the field as a cell a box is displayed. I would like to use the Replace function to remove the box and leave the hard return. I have tried to discover the character map (ALT + number) value but even though the code function returns 13 for this character using this in the replace function Excel does not find the character to replace.--Jim

return the message #reference!
4 replies , 3/22/2006, 10:37:21 AM
I am using the function getpivotdata in excel, and I ask for the total amount on diffrent acounts I have in my pivottabel. If there are no data on the acount I get the answer #reference! because the acount not exist in my pivottabel. Insted I want to get 0. How can I get this result?

When is RETURN not ENTER (and ENTER not RETURN)?
2 replies , 8/17/2007, 7:47:09 PM
I originally posted this over on comp.sys.mac.apps and was directed over here. I'd forgotten about the Microsoft newsgroups, and didn't know if they had any for their Mac products or not. Long story short, Entourage 2004 (fully updated and patched) doesn't seem to process the ENTER key an Apple Macintosh keyboard when composing the body of an e-mail message. I've got a user who has come from a PC background where those keys are identical in functionality and he prefers the numpad-located ENTER key. And--so far--the rest of Office 2004 for Mac does get it right, treating both keys equally. Is there a workaround or solution for this? If it is a bug, where might a person report it? Thanks in advance for any help or suggestions. They are much appreciated. Please don't reply to me via e-mail, as this address doesn't work. William

How Do I Return To TOP of Table of Contents
0 replies , 10/9/2005, 6:36:16 PM
I'm using XP Pro and Office 2000 Premium. I've got a Table of Contents at the start of my document. It's a fairly long document. I would like to insert a hyperlink(?) after a particular heading's paragraph, that when clicked on would return the reader to the top of the Table of Contents. Is there a way to do this in Word? --- Lanny "Trustworthy Computing" is only another example of an Oxymoron!

Lanny Bender
return blank
5 replies , 6/14/2004, 1:53:29 PM
Hi, I am using sumif functions who return "zero" if criteria is not found. How can I let the function return a blank instead of "0"? Thanks

How do I calculate Return on Investment in Excel
3 replies , 10/31/2006, 1:52:02 PM
Is there any function or button that calculates ROI in Excel 2003 or excel 2007(Beta) version? Or is there any procedure to do so?

I want to calcualte in months, rounded in 1, 1.5, 2 month increments. A constant date is being compared to varying dates, (needed to calculate sales $ based on 1 and 1.5 month increments) I used date serial numbers to determine the difference between the the two, however, converting it back to months and rounding it in 1. and .5 increments isn't working. Anyideas???

return significant digits of a cell
2 replies , 9/30/2005, 2:26:07 PM
Is there a function I can used to return the # of significant digits of a cell value. ie. If cell A2 = 1.042, I want cell B2 to equal 3 Thanks, Chace

Return a 0 when vlookup returns #n/a
2 replies , 4/23/2008, 1:44:57 PM
Hi I am doing a vlookup on cell a12 which looks up the entry from a12 in another tab. When the info from cell a12 is not in the other tab reference I am looking up I get a #n/a error. I would like to return a zero or a blank when the lookup does not return a match from cell a12 =VLOOKUP($A12,Sheet2!$D$15:$N$244,B$3,0) Please help. Regards Brian

Replace comma with hard return
6 replies , 8/9/2004, 9:50:15 AM
I have an address that is in a single line with comma's seperating the lines. I want to replce the comma's with hard carriage returns (ALT+ENTER) but I cannot type ALT+ENTER into the replace with box of the replace function. Any idea's? Alternatively, does someone have a macro that will replace one character with another on a string that they could mail me? Paul

Hi Everyone, I have two tables, the first lists letters from AA to ZZ in a lookup type list and when on of these is selected, for example AB, the corresponding cell in the second table (the equivalent AB) is populated with a Y. Table A: Table B: Type Site Controls Type Has Site Control AA (various data) AA Y AB " AB Y DZ " DZ Y LW " LW Y So, as the data is selected in table A, the Y should be populated in table B. I would appreciate the help on this as it would speed up my tasks considerably. Thanks in advance, Remy

Error being returned
4 replies , 1/17/2005, 2:40:33 PM
This following formula is returning #VALUE! =P207/1.4 I have tried to modify with the following: =IF((ISERROR(Sheet1!P207/1.4)," ",(Sheet1!p207/1.4,0)) It is obviously not correct, can someone help with this? Thanks Pat

Return to Last Edit
7 replies , 11/15/2007, 7:18:12 PM
In work, to return to the place where the cursor was last placed (or at least where the last edit was), the instructions in Word Help say to push the F5 key (or maybe shift-F5). This works fine on my iMac, but not at all on my MacBook Pro. All I get is a brief image of the Macbook Pro's volume adjustment icon. Any suggestions?

Returning laptop with ms office
3 replies , 3/1/2009, 12:08:25 PM
I purchased my laptop with MS office trial. I bought a copy of MS office and I entered the product key, however haven't activated. Now I wanna refund my laptop, will I have the 3 pc usages back? Or is there anyway to reimburse that?? -- jean1986 ------------------------------------------------------------------------ jean1986's Profile: http://forums.techarena.in/members/jean1986.htm View this thread: http://forums.techarena.in/ms-office-support/1132224.htm http://forums.techarena.in

ENTOURAGE: No Return Receipt? How could this be?
3 replies , 8/25/2005, 6:03:51 PM
ENTOURAGE: No Return Receipt? How could this be? After 10+ years of the robust, but only nominally-mac-supported Eudora Pro, we recently migrated over to Entourage for our e-mail-intensive business. Anong other requirements, we use more than 20 separate signatures, and the Mac-resident MAIL program just didn't seem hearty enough for our needs. I've searched Entourage up, down, and six ways to Sunday, looking for the ability to send an e-mail with RETURN RECEIPT. Could it really be true that this crucial tool was not included in Entourage, or am I using the wrong program? Thanks for any help anyone may be able to offer, either providing the hidden key to unlock Return Receipt Facility in Entourage, or pointing us to a muscular and versatile, great Mac-native or Mac-friendly e-mail client that will.. Signed, Bewildered in The Northwest.

VLOOKUP Return Incorrect Values
2 replies , 7/13/2004, 6:35:50 PM
I've followed the Resolution Methods from Microsoft Knowledge Base Article - 181201, but I am getting "N/A" results where I shouldn't be. My formula (=VLOOKUP (C4,$I$3:$J$162,2,FALSE) should find a match for 84 out of 159 items but it is only finding 1.

Glenn Weeks
Cursor not returning to the left
3 replies , 3/23/2005, 12:22:13 AM
From what I remember, tell me if I'm wrong, when I would have a piece of text I wanted centered, I would align center that text, and hit return and the cursor would go to the left (or left alignment) on next line. For some reason, it seems to stay on the center alignment after hitting return. Any help appreciated. Thanks! Kevs OS 10.3.8 Office 2004 OS 10.3.8 Office 2004

Result not being returned
8 replies , 2/19/2005, 4:43:08 PM
=IF(OR($J74>0,$K$22="QS"),0,$J74) the above formula should display the value from J74 when QS is displayed in K22 Could someone tell me why I am not getting a result. Thanks

Help! I was using Office 2000 and I unwittingly activated the trial version of MS Office S & T Edition 2003. Now the trial period is over. I do NOT want to purchase the full version. Instead, I want to return to my Office 2000. I read on MS.com that I need to uninstall the trial version THEN reinstall Office 2000. If so will my emails and docs on the trial version still be there? Has anyone done this? Advice? Please. -- frustrated

Limiting a returned calc to 195
4 replies , 11/6/2007, 4:36:11 PM
I have a complex formula, i need to limit the result of the calculation to 195 maximum..its probably dead simple..but how do i do it? cheers Steve

Mr Sandman
Return Cell to Blank Value
2 replies , 4/27/2004, 9:19:02 AM
When putting in a formula say IF(ISERROR(b2/c2),"",(b2/c2)) if there is an error the cell value is seen as blank but when plotting the data to graph shows up as zero. What I require is that the cell is left unformatted so when plotting this data onto a graph it will not appare as a data point. Is there another way to do this?

Carriage return in macros
1 replies , 11/15/2004, 1:43:35 PM
Hi I would like to write a list of words on a TextBox of a UserForm; ho can I specifiy the Carriage return character in the "TextBox.Text= sentence? Thanks! -- Manu Pala ----------------------------------------------------------------------- Manu Palao's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1620 View this thread: http://www.excelforum.com/showthread.php?threadid=27827

Manu Palao
Returning Yesterday
5 replies , 5/12/2008, 3:05:01 PM
Hello I want my spreadsheet to return YESTERDAY's date, not TODAY's. I believe it's not possible to embed TODAY() in a formula, so can anyone tell me how to return YESTERDAY'S date into a cell? Thank you. Keith