| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Arithmatic functions with date format...angry results
Can anyone tell me how to do the following:
I have a col. A and Col b with dates in the following format: 12/5/2008 12:00:00 AM and 11/19/2008 0:00 What I need to make a formula in the next col that will tell me how many days apart these two dates are. Basically, just need to subtract the two dates and display the number of day between the two dates.. Any thoughts on how to do this? Thanks a bunch for all the help!! -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200901/1 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Arithmatic functions with date format...angry results
As long as those are entered as true date/times in Excel, you could do this:
=INT(B1-A1) and format the cell as General. Hope this helps. Pete "chris23892 via OfficeKB.com" <u39649@uwe wrote in message news:8fe388a225d78@uwe... Can anyone tell me how to do the following: I have a col. A and Col b with dates in the following format: 12/5/2008 12:00:00 AM and 11/19/2008 0:00 What I need to make a formula in the next col that will tell me how many days apart these two dates are. Basically, just need to subtract the two dates and display the number of day between the two dates.. Any thoughts on how to do this? Thanks a bunch for all the help!! -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200901/1 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Arithmatic functions with date format...angry results
Note that 12:00:00 AM is treated the same as 0:00:00.
A formula of =A2-B2 will work, just need to format the cell to general or number, as XL will default to thinking you want a date. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "chris23892 via OfficeKB.com" wrote: Can anyone tell me how to do the following: I have a col. A and Col b with dates in the following format: 12/5/2008 12:00:00 AM and 11/19/2008 0:00 What I need to make a formula in the next col that will tell me how many days apart these two dates are. Basically, just need to subtract the two dates and display the number of day between the two dates.. Any thoughts on how to do this? Thanks a bunch for all the help!! -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200901/1 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Arithmatic functions with date format...angry results
You rock! Worked like a charm. I was using =ABS(B1-A1) and not formatting it
as genral.....gave me strange date format results. Your's worked great :) Pete_UK wrote: As long as those are entered as true date/times in Excel, you could do this: =INT(B1-A1) and format the cell as General. Hope this helps. Pete Can anyone tell me how to do the following: [quoted text clipped - 9 lines] Any thoughts on how to do this? Thanks a bunch for all the help!! -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200901/1 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Arithmatic functions with date format...angry results
=B1-A1, and format the result as number or general.
Make sure that your input cells are really Excel date/time, and not text. If, for example, you have a non-breaking space CHAR(160) instead of a real space, Excel will treat it as text. If in doubt, check with =ISNUMBER(cellref) and =ISTEXT(cellref). -- David Biddulph "chris23892 via OfficeKB.com" <u39649@uwe wrote in message news:8fe388a225d78@uwe... Can anyone tell me how to do the following: I have a col. A and Col b with dates in the following format: 12/5/2008 12:00:00 AM and 11/19/2008 0:00 What I need to make a formula in the next col that will tell me how many days apart these two dates are. Basically, just need to subtract the two dates and display the number of day between the two dates.. Any thoughts on how to do this? Thanks a bunch for all the help!! -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200901/1 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Arithmatic functions with date format...angry results
You're welcome, Chris - thanks for feeding back.
Dates are stored in Excel as integers (number of elapsed days since some reference date), whereas times are stored as fractions of a 24-hour day. Hence you don't need the fractional part. You might also consider this variation: =INT(B1)-INT(A1) again, formatted as General. Pete "chris23892 via OfficeKB.com" <u39649@uwe wrote in message news:8fe3d61e8d31d@uwe... You rock! Worked like a charm. I was using =ABS(B1-A1) and not formatting it as genral.....gave me strange date format results. Your's worked great :) Pete_UK wrote: As long as those are entered as true date/times in Excel, you could do this: =INT(B1-A1) and format the cell as General. Hope this helps. Pete Can anyone tell me how to do the following: [quoted text clipped - 9 lines] Any thoughts on how to do this? Thanks a bunch for all the help!! -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200901/1 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Arithmatic functions with date format...angry results
I guess it depends on what result they're looking for:
1/1/2009 11:59 PM 1/5/2009 11:00 AM =INT(B1-A1) = 3 =INT(B1)-INT(A1) = 4 -- Biff Microsoft Excel MVP "Pete_UK" wrote in message ... As long as those are entered as true date/times in Excel, you could do this: =INT(B1-A1) and format the cell as General. Hope this helps. Pete "chris23892 via OfficeKB.com" <u39649@uwe wrote in message news:8fe388a225d78@uwe... Can anyone tell me how to do the following: I have a col. A and Col b with dates in the following format: 12/5/2008 12:00:00 AM and 11/19/2008 0:00 What I need to make a formula in the next col that will tell me how many days apart these two dates are. Basically, just need to subtract the two dates and display the number of day between the two dates.. Any thoughts on how to do this? Thanks a bunch for all the help!! -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200901/1 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Arithmatic functions with date format...angry results
I pointed this out in my follow-up reply, but thanks for giving the example.
Pete "T. Valko" wrote in message ... I guess it depends on what result they're looking for: 1/1/2009 11:59 PM 1/5/2009 11:00 AM =INT(B1-A1) = 3 =INT(B1)-INT(A1) = 4 -- Biff Microsoft Excel MVP "Pete_UK" wrote in message ... As long as those are entered as true date/times in Excel, you could do this: =INT(B1-A1) and format the cell as General. Hope this helps. Pete "chris23892 via OfficeKB.com" <u39649@uwe wrote in message news:8fe388a225d78@uwe... Can anyone tell me how to do the following: I have a col. A and Col b with dates in the following format: 12/5/2008 12:00:00 AM and 11/19/2008 0:00 What I need to make a formula in the next col that will tell me how many days apart these two dates are. Basically, just need to subtract the two dates and display the number of day between the two dates.. Any thoughts on how to do this? Thanks a bunch for all the help!! -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200901/1 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Using intermediate results in Worksheet functions | Excel Worksheet Functions | |||
| Created IF functions - Need sum of results | Excel Worksheet Functions | |||
| Database Functions - Strange results | Excel Worksheet Functions | |||
| Text Time Coversion Arithmatic | Excel Discussion (Misc queries) | |||
| functions are not displaying the results | Excel Worksheet Functions | |||
Linear Mode
