Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I get AutoFill to fill dates with time without rounding.
I've run into a problem when using autofill in Excel. I'm trying to autofill
dates in the following format dd/mm/yyyy h:mm:ss. For example, I have a measurement time of 02/10/2008 12:28:00 and the next time is: 02/10/2008 12:28:15 I've highlighted both of these cells and then dragged the bottom corner to autofill this pattern. This works for awhile until I get: 02/10/2008 12:40:29 instead of 02/10/2008 12:40:30 This doesn't make a big difference right away but after continuing this for a couple of hours the time is way off. Is Excel all of a sudden rounding this number? Is there a way of making it so that exactly 15 seconds are added each time? Any help would be greatly appreciated! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I get AutoFill to fill dates with time without rounding.
To force Excel to use 15 second intervals, use a formula, as in:
=a1+time(0,0,15) Then drag this down. Regards, Fred. "PCF" wrote in message ... I've run into a problem when using autofill in Excel. I'm trying to autofill dates in the following format dd/mm/yyyy h:mm:ss. For example, I have a measurement time of 02/10/2008 12:28:00 and the next time is: 02/10/2008 12:28:15 I've highlighted both of these cells and then dragged the bottom corner to autofill this pattern. This works for awhile until I get: 02/10/2008 12:40:29 instead of 02/10/2008 12:40:30 This doesn't make a big difference right away but after continuing this for a couple of hours the time is way off. Is Excel all of a sudden rounding this number? Is there a way of making it so that exactly 15 seconds are added each time? Any help would be greatly appreciated! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I get AutoFill to fill dates with time without rounding.
I've also run into this behavior when "atuofilling" times. Do it this way:
Enter your first date/time in a cell: A1 = 02/10/2008 12:28:00 Enter this formula in A2 and copy down as needed: =A1+TIME(0,0,15) After you're done then you can convert the formulas to constants: Select the range of formulas Goto EditCopy Then EditPaste SpecialValuesOK -- Biff Microsoft Excel MVP "PCF" wrote in message ... I've run into a problem when using autofill in Excel. I'm trying to autofill dates in the following format dd/mm/yyyy h:mm:ss. For example, I have a measurement time of 02/10/2008 12:28:00 and the next time is: 02/10/2008 12:28:15 I've highlighted both of these cells and then dragged the bottom corner to autofill this pattern. This works for awhile until I get: 02/10/2008 12:40:29 instead of 02/10/2008 12:40:30 This doesn't make a big difference right away but after continuing this for a couple of hours the time is way off. Is Excel all of a sudden rounding this number? Is there a way of making it so that exactly 15 seconds are added each time? Any help would be greatly appreciated! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I get AutoFill to fill dates with time without rounding.
Hi Biff,
Do know anything about why this happens and is it different in separate versions? In XL2000 replicating the OP's data I got down to A3104 before it kicked in showing 03/10/2008 1:24:01 yet it seems the OP only got to A51 If it was just a matter of the binary to decimal problem you would expect to get the same result no matter what version. Even more puzzling is if I start with these two like this without inputting a date 12:00:00 12:00:15 It goes all the way to A65536 without missing a beat. Regards Martin "T. Valko" wrote in message ... I've also run into this behavior when "atuofilling" times. Do it this way: Enter your first date/time in a cell: A1 = 02/10/2008 12:28:00 Enter this formula in A2 and copy down as needed: =A1+TIME(0,0,15) After you're done then you can convert the formulas to constants: Select the range of formulas Goto EditCopy Then EditPaste SpecialValuesOK -- Biff Microsoft Excel MVP "PCF" wrote in message ... I've run into a problem when using autofill in Excel. I'm trying to autofill dates in the following format dd/mm/yyyy h:mm:ss. For example, I have a measurement time of 02/10/2008 12:28:00 and the next time is: 02/10/2008 12:28:15 I've highlighted both of these cells and then dragged the bottom corner to autofill this pattern. This works for awhile until I get: 02/10/2008 12:40:29 instead of 02/10/2008 12:40:30 This doesn't make a big difference right away but after continuing this for a couple of hours the time is way off. Is Excel all of a sudden rounding this number? Is there a way of making it so that exactly 15 seconds are added each time? Any help would be greatly appreciated! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I get AutoFill to fill dates with time without rounding.
If it was just a matter of the binary to decimal problem you would expect
to get the same result no matter what version. You would expect that but it depends on how you "autofill". Try this: Enter 12:00 AM in A1. Enter 1:00 AM is A2 Select both A1 and A2 and drag down to A7. Now, select cell A7 *only* and drag down to A10. Now, select cell A10 *only* and drag down to A14. Now, select cell A14 *only* and drag down to A18 Now, select cell A18 *only* and drag down to A19 Now, select cell A19 *only* and drag down to A24. Now, enter this formula in B1 and copy down to B24: =A1=TIME(ROWS($1:1)-1,0,0) Every result should be TRUE but they're not! (at least, in my version of Excel 2002 I get several FALSE results) Now, enter 12:00 Am in D1 and 1:00 AM in D2. Select both D1 and D2 and drag down to D24 *without* stopping. Enter this formula in E1 and copy down to E24: =D1=TIME(ROWS($1:1)-1,0,0) Every result is TRUE as you would expect! I've gotten "burned" on this behavior in the past! I couldn't figure out why 23:00 < 23:00. It seems that the autofill "resets" or "adjusts" somehow once you stop and then restart. -- Biff Microsoft Excel MVP "MartinW" wrote in message ... Hi Biff, Do know anything about why this happens and is it different in separate versions? In XL2000 replicating the OP's data I got down to A3104 before it kicked in showing 03/10/2008 1:24:01 yet it seems the OP only got to A51 If it was just a matter of the binary to decimal problem you would expect to get the same result no matter what version. Even more puzzling is if I start with these two like this without inputting a date 12:00:00 12:00:15 It goes all the way to A65536 without missing a beat. Regards Martin "T. Valko" wrote in message ... I've also run into this behavior when "atuofilling" times. Do it this way: Enter your first date/time in a cell: A1 = 02/10/2008 12:28:00 Enter this formula in A2 and copy down as needed: =A1+TIME(0,0,15) After you're done then you can convert the formulas to constants: Select the range of formulas Goto EditCopy Then EditPaste SpecialValuesOK -- Biff Microsoft Excel MVP "PCF" wrote in message ... I've run into a problem when using autofill in Excel. I'm trying to autofill dates in the following format dd/mm/yyyy h:mm:ss. For example, I have a measurement time of 02/10/2008 12:28:00 and the next time is: 02/10/2008 12:28:15 I've highlighted both of these cells and then dragged the bottom corner to autofill this pattern. This works for awhile until I get: 02/10/2008 12:40:29 instead of 02/10/2008 12:40:30 This doesn't make a big difference right away but after continuing this for a couple of hours the time is way off. Is Excel all of a sudden rounding this number? Is there a way of making it so that exactly 15 seconds are added each time? Any help would be greatly appreciated! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I get AutoFill to fill dates with time without rounding.
Interesting, it's definitely one to keep an eye on. I'm surprised
I haven't run into it before. I can think of times when I should have and that makes me a little uneasy that maybe my checking and double checking wasn't up to scratch although I am usally very thorough in that aspect. Hopefully I avoided it by pure dumb luck.<g At least now I know to avoid it in the future. Thanks Martin "T. Valko" wrote in message ... If it was just a matter of the binary to decimal problem you would expect to get the same result no matter what version. You would expect that but it depends on how you "autofill". Try this: Enter 12:00 AM in A1. Enter 1:00 AM is A2 Select both A1 and A2 and drag down to A7. Now, select cell A7 *only* and drag down to A10. Now, select cell A10 *only* and drag down to A14. Now, select cell A14 *only* and drag down to A18 Now, select cell A18 *only* and drag down to A19 Now, select cell A19 *only* and drag down to A24. Now, enter this formula in B1 and copy down to B24: =A1=TIME(ROWS($1:1)-1,0,0) Every result should be TRUE but they're not! (at least, in my version of Excel 2002 I get several FALSE results) Now, enter 12:00 Am in D1 and 1:00 AM in D2. Select both D1 and D2 and drag down to D24 *without* stopping. Enter this formula in E1 and copy down to E24: =D1=TIME(ROWS($1:1)-1,0,0) Every result is TRUE as you would expect! I've gotten "burned" on this behavior in the past! I couldn't figure out why 23:00 < 23:00. It seems that the autofill "resets" or "adjusts" somehow once you stop and then restart. -- Biff Microsoft Excel MVP "MartinW" wrote in message ... Hi Biff, Do know anything about why this happens and is it different in separate versions? In XL2000 replicating the OP's data I got down to A3104 before it kicked in showing 03/10/2008 1:24:01 yet it seems the OP only got to A51 If it was just a matter of the binary to decimal problem you would expect to get the same result no matter what version. Even more puzzling is if I start with these two like this without inputting a date 12:00:00 12:00:15 It goes all the way to A65536 without missing a beat. Regards Martin "T. Valko" wrote in message ... I've also run into this behavior when "atuofilling" times. Do it this way: Enter your first date/time in a cell: A1 = 02/10/2008 12:28:00 Enter this formula in A2 and copy down as needed: =A1+TIME(0,0,15) After you're done then you can convert the formulas to constants: Select the range of formulas Goto EditCopy Then EditPaste SpecialValuesOK -- Biff Microsoft Excel MVP "PCF" wrote in message ... I've run into a problem when using autofill in Excel. I'm trying to autofill dates in the following format dd/mm/yyyy h:mm:ss. For example, I have a measurement time of 02/10/2008 12:28:00 and the next time is: 02/10/2008 12:28:15 I've highlighted both of these cells and then dragged the bottom corner to autofill this pattern. This works for awhile until I get: 02/10/2008 12:40:29 instead of 02/10/2008 12:40:30 This doesn't make a big difference right away but after continuing this for a couple of hours the time is way off. Is Excel all of a sudden rounding this number? Is there a way of making it so that exactly 15 seconds are added each time? Any help would be greatly appreciated! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Generating dates Using Autofill | Excel Discussion (Misc queries) | |||
autofill on fixed dates | Excel Worksheet Functions | |||
Autofill default: I want Fill Without Formatting | Excel Discussion (Misc queries) | |||
autofill next sheet with next set of dates | Excel Worksheet Functions | |||
Can I use AutoFill or a formula to fill a series of letters? | Excel Discussion (Misc queries) |