Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF using Dates
I have wriiten UDF including the =NOW() function in it. When checking using
De-bug it places the result of the UDF in the workbook correctly. When calling the UDF in the workbook it gives VALUE error. Only have this problem with Date Functions. Anyone has a solution please Thanks Guys..Jim. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF using Dates
Post the UDF.
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jim Dean" <Jim wrote in message ... I have wriiten UDF including the =NOW() function in it. When checking using De-bug it places the result of the UDF in the workbook correctly. When calling the UDF in the workbook it gives VALUE error. Only have this problem with Date Functions. Anyone has a solution please Thanks Guys..Jim. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF using Dates
Hi Bob see below cheers Jim
Function FreezeDate() ActiveCell.Select ActiveCell.FormulaR1C1 = "=NOW()" 'this macro places date then copies it to the same cell and changes the cell to value. 'this allows the date to remain frozen Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Selection.NumberFormat = "d/mm/yyyy;@" End Function "Bob Phillips" wrote: Post the UDF. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jim Dean" <Jim wrote in message ... I have wriiten UDF including the =NOW() function in it. When checking using De-bug it places the result of the UDF in the workbook correctly. When calling the UDF in the workbook it gives VALUE error. Only have this problem with Date Functions. Anyone has a solution please Thanks Guys..Jim. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF using Dates
Jim,
You can't do things like that with UDFs. They return a value into the cell, they cannot change the cell value as you are trying to do, that would overwrite the function that is running. Why not just use =TEXT(TODAY(),"d/mm/yyyy;@") in the cell? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jim Dean" wrote in message ... Hi Bob see below cheers Jim Function FreezeDate() ActiveCell.Select ActiveCell.FormulaR1C1 = "=NOW()" 'this macro places date then copies it to the same cell and changes the cell to value. 'this allows the date to remain frozen Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Selection.NumberFormat = "d/mm/yyyy;@" End Function "Bob Phillips" wrote: Post the UDF. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jim Dean" <Jim wrote in message ... I have wriiten UDF including the =NOW() function in it. When checking using De-bug it places the result of the UDF in the workbook correctly. When calling the UDF in the workbook it gives VALUE error. Only have this problem with Date Functions. Anyone has a solution please Thanks Guys..Jim. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF using Dates
Hi Bob
Give me the name of your boss and I tell him to pay you double. Great work Pal and thanks, it works like magic Keep well Cheers from sunny Sydney. "Bob Phillips" wrote: Jim, You can't do things like that with UDFs. They return a value into the cell, they cannot change the cell value as you are trying to do, that would overwrite the function that is running. Why not just use =TEXT(TODAY(),"d/mm/yyyy;@") in the cell? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jim Dean" wrote in message ... Hi Bob see below cheers Jim Function FreezeDate() ActiveCell.Select ActiveCell.FormulaR1C1 = "=NOW()" 'this macro places date then copies it to the same cell and changes the cell to value. 'this allows the date to remain frozen Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Selection.NumberFormat = "d/mm/yyyy;@" End Function "Bob Phillips" wrote: Post the UDF. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jim Dean" <Jim wrote in message ... I have wriiten UDF including the =NOW() function in it. When checking using De-bug it places the result of the UDF in the workbook correctly. When calling the UDF in the workbook it gives VALUE error. Only have this problem with Date Functions. Anyone has a solution please Thanks Guys..Jim. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF using Dates
This doesn't keep the date frozen, though.
Is there a reason, you just don't hit ctrl-; and format the cell the way you like? Jim Dean wrote: Hi Bob Give me the name of your boss and I tell him to pay you double. Great work Pal and thanks, it works like magic Keep well Cheers from sunny Sydney. "Bob Phillips" wrote: Jim, You can't do things like that with UDFs. They return a value into the cell, they cannot change the cell value as you are trying to do, that would overwrite the function that is running. Why not just use =TEXT(TODAY(),"d/mm/yyyy;@") in the cell? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jim Dean" wrote in message ... Hi Bob see below cheers Jim Function FreezeDate() ActiveCell.Select ActiveCell.FormulaR1C1 = "=NOW()" 'this macro places date then copies it to the same cell and changes the cell to value. 'this allows the date to remain frozen Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Selection.NumberFormat = "d/mm/yyyy;@" End Function "Bob Phillips" wrote: Post the UDF. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jim Dean" <Jim wrote in message ... I have wriiten UDF including the =NOW() function in it. When checking using De-bug it places the result of the UDF in the workbook correctly. When calling the UDF in the workbook it gives VALUE error. Only have this problem with Date Functions. Anyone has a solution please Thanks Guys..Jim. -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF using Dates
Dave
Sorry i dont follow what you meant. I agree it did not work. Jim. "Dave Peterson" wrote: This doesn't keep the date frozen, though. Is there a reason, you just don't hit ctrl-; and format the cell the way you like? Jim Dean wrote: Hi Bob Give me the name of your boss and I tell him to pay you double. Great work Pal and thanks, it works like magic Keep well Cheers from sunny Sydney. "Bob Phillips" wrote: Jim, You can't do things like that with UDFs. They return a value into the cell, they cannot change the cell value as you are trying to do, that would overwrite the function that is running. Why not just use =TEXT(TODAY(),"d/mm/yyyy;@") in the cell? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jim Dean" wrote in message ... Hi Bob see below cheers Jim Function FreezeDate() ActiveCell.Select ActiveCell.FormulaR1C1 = "=NOW()" 'this macro places date then copies it to the same cell and changes the cell to value. 'this allows the date to remain frozen Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Selection.NumberFormat = "d/mm/yyyy;@" End Function "Bob Phillips" wrote: Post the UDF. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jim Dean" <Jim wrote in message ... I have wriiten UDF including the =NOW() function in it. When checking using De-bug it places the result of the UDF in the workbook correctly. When calling the UDF in the workbook it gives VALUE error. Only have this problem with Date Functions. Anyone has a solution please Thanks Guys..Jim. -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF using Dates
Dave
Yes the reason is I have so many to update manually that i want to do this in a function saves me a ton of time. see the statement earlier. cheers jim. "Jim Dean" wrote: Dave Sorry i dont follow what you meant. I agree it did not work. Jim. "Dave Peterson" wrote: This doesn't keep the date frozen, though. Is there a reason, you just don't hit ctrl-; and format the cell the way you like? Jim Dean wrote: Hi Bob Give me the name of your boss and I tell him to pay you double. Great work Pal and thanks, it works like magic Keep well Cheers from sunny Sydney. "Bob Phillips" wrote: Jim, You can't do things like that with UDFs. They return a value into the cell, they cannot change the cell value as you are trying to do, that would overwrite the function that is running. Why not just use =TEXT(TODAY(),"d/mm/yyyy;@") in the cell? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jim Dean" wrote in message ... Hi Bob see below cheers Jim Function FreezeDate() ActiveCell.Select ActiveCell.FormulaR1C1 = "=NOW()" 'this macro places date then copies it to the same cell and changes the cell to value. 'this allows the date to remain frozen Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Selection.NumberFormat = "d/mm/yyyy;@" End Function "Bob Phillips" wrote: Post the UDF. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jim Dean" <Jim wrote in message ... I have wriiten UDF including the =NOW() function in it. When checking using De-bug it places the result of the UDF in the workbook correctly. When calling the UDF in the workbook it gives VALUE error. Only have this problem with Date Functions. Anyone has a solution please Thanks Guys..Jim. -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF using Dates
I don't see anything in your earlier posts that describe what you're trying to
accomplish. If you want a cell in a row to change when something in that row changes, maybe you can look at the way J.E. McGimpsey approached it: http://www.mcgimpsey.com/excel/timestamp.html Jim Dean wrote: Dave Yes the reason is I have so many to update manually that i want to do this in a function saves me a ton of time. see the statement earlier. cheers jim. "Jim Dean" wrote: Dave Sorry i dont follow what you meant. I agree it did not work. Jim. "Dave Peterson" wrote: This doesn't keep the date frozen, though. Is there a reason, you just don't hit ctrl-; and format the cell the way you like? Jim Dean wrote: Hi Bob Give me the name of your boss and I tell him to pay you double. Great work Pal and thanks, it works like magic Keep well Cheers from sunny Sydney. "Bob Phillips" wrote: Jim, You can't do things like that with UDFs. They return a value into the cell, they cannot change the cell value as you are trying to do, that would overwrite the function that is running. Why not just use =TEXT(TODAY(),"d/mm/yyyy;@") in the cell? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jim Dean" wrote in message ... Hi Bob see below cheers Jim Function FreezeDate() ActiveCell.Select ActiveCell.FormulaR1C1 = "=NOW()" 'this macro places date then copies it to the same cell and changes the cell to value. 'this allows the date to remain frozen Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Selection.NumberFormat = "d/mm/yyyy;@" End Function "Bob Phillips" wrote: Post the UDF. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jim Dean" <Jim wrote in message ... I have wriiten UDF including the =NOW() function in it. When checking using De-bug it places the result of the UDF in the workbook correctly. When calling the UDF in the workbook it gives VALUE error. Only have this problem with Date Functions. Anyone has a solution please Thanks Guys..Jim. -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF using Dates
Hi Dave
I am realy trying to do this : I need to get excel to enter to-days date and then freeze this date so is does not update later. I dont want to copy-paste special-values. I need to have it in a function statement. I tried =IF(C17="not completed","",TEXT(TODAY(),"d/mm/yyyy;@")) but it still updates even though the cell format is text. I think i will need to write a macro, but have difficulty linking it with the worksheet. I am reading thru' J.E.McGimpsey reply. cheers. "Dave Peterson" wrote: I don't see anything in your earlier posts that describe what you're trying to accomplish. If you want a cell in a row to change when something in that row changes, maybe you can look at the way J.E. McGimpsey approached it: http://www.mcgimpsey.com/excel/timestamp.html Jim Dean wrote: Dave Yes the reason is I have so many to update manually that i want to do this in a function saves me a ton of time. see the statement earlier. cheers jim. "Jim Dean" wrote: Dave Sorry i dont follow what you meant. I agree it did not work. Jim. "Dave Peterson" wrote: This doesn't keep the date frozen, though. Is there a reason, you just don't hit ctrl-; and format the cell the way you like? Jim Dean wrote: Hi Bob Give me the name of your boss and I tell him to pay you double. Great work Pal and thanks, it works like magic Keep well Cheers from sunny Sydney. "Bob Phillips" wrote: Jim, You can't do things like that with UDFs. They return a value into the cell, they cannot change the cell value as you are trying to do, that would overwrite the function that is running. Why not just use =TEXT(TODAY(),"d/mm/yyyy;@") in the cell? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jim Dean" wrote in message ... Hi Bob see below cheers Jim Function FreezeDate() ActiveCell.Select ActiveCell.FormulaR1C1 = "=NOW()" 'this macro places date then copies it to the same cell and changes the cell to value. 'this allows the date to remain frozen Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Selection.NumberFormat = "d/mm/yyyy;@" End Function "Bob Phillips" wrote: Post the UDF. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jim Dean" <Jim wrote in message ... I have wriiten UDF including the =NOW() function in it. When checking using De-bug it places the result of the UDF in the workbook correctly. When calling the UDF in the workbook it gives VALUE error. Only have this problem with Date Functions. Anyone has a solution please Thanks Guys..Jim. -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF using Dates
I think J.E.'s event macro is what you want.
If you have trouble implementing it, post back with your code. Jim Dean wrote: Hi Dave I am realy trying to do this : I need to get excel to enter to-days date and then freeze this date so is does not update later. I dont want to copy-paste special-values. I need to have it in a function statement. I tried =IF(C17="not completed","",TEXT(TODAY(),"d/mm/yyyy;@")) but it still updates even though the cell format is text. I think i will need to write a macro, but have difficulty linking it with the worksheet. I am reading thru' J.E.McGimpsey reply. cheers. "Dave Peterson" wrote: I don't see anything in your earlier posts that describe what you're trying to accomplish. If you want a cell in a row to change when something in that row changes, maybe you can look at the way J.E. McGimpsey approached it: http://www.mcgimpsey.com/excel/timestamp.html Jim Dean wrote: Dave Yes the reason is I have so many to update manually that i want to do this in a function saves me a ton of time. see the statement earlier. cheers jim. "Jim Dean" wrote: Dave Sorry i dont follow what you meant. I agree it did not work. Jim. "Dave Peterson" wrote: This doesn't keep the date frozen, though. Is there a reason, you just don't hit ctrl-; and format the cell the way you like? Jim Dean wrote: Hi Bob Give me the name of your boss and I tell him to pay you double. Great work Pal and thanks, it works like magic Keep well Cheers from sunny Sydney. "Bob Phillips" wrote: Jim, You can't do things like that with UDFs. They return a value into the cell, they cannot change the cell value as you are trying to do, that would overwrite the function that is running. Why not just use =TEXT(TODAY(),"d/mm/yyyy;@") in the cell? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jim Dean" wrote in message ... Hi Bob see below cheers Jim Function FreezeDate() ActiveCell.Select ActiveCell.FormulaR1C1 = "=NOW()" 'this macro places date then copies it to the same cell and changes the cell to value. 'this allows the date to remain frozen Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Selection.NumberFormat = "d/mm/yyyy;@" End Function "Bob Phillips" wrote: Post the UDF. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jim Dean" <Jim wrote in message ... I have wriiten UDF including the =NOW() function in it. When checking using De-bug it places the result of the UDF in the workbook correctly. When calling the UDF in the workbook it gives VALUE error. Only have this problem with Date Functions. Anyone has a solution please Thanks Guys..Jim. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linking computer dates (time) to spreadsheet dates that have formu | Excel Worksheet Functions | |||
compare 2 tables of dates to find the preceding dates | Excel Worksheet Functions | |||
Toggle a range of Julian dates to Gregorian Dates and Back | Excel Programming | |||
Identifying unique dates in a range of cells containing dates... | Excel Discussion (Misc queries) | |||
Calculating number of days between two dates that fall between two other dates | Excel Discussion (Misc queries) |