your explanation is CLOSE!! Excel date is the number of days since 1/1/1900 (or SINCE 31/12/1899, since 1/1/1900 is day (1) ) So, December 31, 1900 is actually day 366 (1900 was a Leap Year)
And... I suppose if it was actually taken as the number of days since the beginning of TIME, We'd probably have to use an XLong Integer or even XXLong Integer when doing calculations! ;o) Paul ----------------------------------------- “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley ----------------------------------------- ________________________________ From: David Grugeon <da...@grugeon.com.au> To: "excel-macros@googlegroups.com" <excel-macros@googlegroups.com> Sent: Fri, February 22, 2013 7:23:40 AM Subject: Re: $$Excel-Macros$$ Date hour value problem! OK A date/time value is a number of days since the beginning of time (31/12/1900 I think) Time is a fraction of a day. so i hour is 1/24 My formula was =(int(B3)+mod(B3,1)/.6)/24 Lets take a value entered as 4.15 which we want to represent as 4:15 or 4 hours and 15 minutes. Int(B3) gives us the 4 Mod(B3,1) gives us the 0.15 part this is equivalent to 15/100. However we want 15/60 so we divide it by 0.6 This gives 0.25. Add them together and we have 4.25 or 4 1/4 hours. Now divide by 24 to convert to days. So 4 hours 15 minutes since the beginning of time is expressed by the number. 0.1770833 or thereabouts. When we apply a time format h:mm to this it displays as 4:15. QED RegardsDavid Grugeon On 22 February 2013 21:30, David Grugeon <da...@grugeon.com.au> wrote: try this > > >If the value is in B3 use then formula > > >=(int(B3)+mod(B3,1)/.6)/24 >then format as h:mm > > >RegardsDavid Grugeon > > > > > >On 22 February 2013 20:36, The Viper <viper....@gmail.com> wrote: > >excel will not hold a value as 2.30 with full decimal(2). We can derive the >desired result by formula if you intend. > >>share us a sample file for more assistance >> >> >> >>On Fri, Feb 22, 2013 at 3:53 PM, <chhajersand...@gmail.com> wrote: >> >>Thanks for quick suggestion but it is not full proof where there is only 8 is >>written it is not working. Again for strange reason where 2.30 was written >>after >>converting it become 2:03. Don't know why! Pls suggest! >> >>> >>> >>>Sandeep Chhajer. >>>Sent on my BlackBerry® from Vodafone ________________________________ >>>From: The Viper <viper....@gmail.com> >>>Sender: excel-macros@googlegroups.com >>>Date: Fri, 22 Feb 2013 15:48:08 +0530 >>>To: <excel-macros@googlegroups.com> >>>ReplyTo: excel-macros@googlegroups.com >>>Subject: Re: $$Excel-Macros$$ Date hour value problem! >>> >>>select the data and press ctrl+h >>>put .(dot) in "Find what" and put ":" in "Replace with" >>>click Replace all >>> >>> >>>On Fri, Feb 22, 2013 at 3:41 PM, <chhajersand...@gmail.com> wrote: >>> >>>Dear excel guru, >>>>I have a problem...I want my hour to be written as 4:45. But I entered >>>>20000 >>>>data as 4.45. Now if I am changing it to [h]:mm format through custom >>>>format I >>>>am getting 10:48 AM please help how can I convert it to 4.45 to 4:45. >>>> >>>>Thanking you. >>>>Sandeep Chhajer. >>>>Sent on my BlackBerry® from Vodafone >>>> >>>>-- >>>>Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s >>>>=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ >>>>https://www.facebook.com/discussexcel >>>> >>>>FORUM RULES >>>> >>>>1) Use concise, accurate thread titles. Poor thread titles, like Please >>>>Help, >>>>Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not >>>>get >>>>quick attention or may not be answered. >>>>2) Don't post a question in the thread of another member. >>>>3) Don't post questions regarding breaking or bypassing any security measure. >>>>4) Acknowledge the responses you receive, good or bad. >>>>5) Jobs posting is not allowed. >>>>6) Sharing copyrighted material and their links is not allowed. >>>> >>>>NOTE : Don't ever post confidential data in a workbook. Forum owners and >>>>members are not responsible for any loss. >>>>--- >>>>You received this message because you are subscribed to the Google Groups >>>>"MS >>>>EXCEL AND VBA MACROS" group. >>>>To unsubscribe from this group and stop receiving emails from it, send an >>>>email >>>>to excel-macros+unsubscr...@googlegroups.com. >>>>To post to this group, send email to excel-macros@googlegroups.com. >>>>Visit this group at http://groups.google.com/group/excel-macros?hl=en. >>>>For more options, visit https://groups.google.com/groups/opt_out. >>>> >>>> >>>> >>>-- >>>Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s >>>=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ >>>https://www.facebook.com/discussexcel >>> >>>FORUM RULES >>> >>>1) Use concise, accurate thread titles. Poor thread titles, like Please >>>Help, >>>Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not >>>get >>>quick attention or may not be answered. >>>2) Don't post a question in the thread of another member. >>>3) Don't post questions regarding breaking or bypassing any security measure. >>>4) Acknowledge the responses you receive, good or bad. >>>5) Jobs posting is not allowed. >>>6) Sharing copyrighted material and their links is not allowed. >>> >>>NOTE : Don't ever post confidential data in a workbook. Forum owners and >>>members >>>are not responsible for any loss. >>>--- >>>You received this message because you are subscribed to the Google Groups >>>"MS >>>EXCEL AND VBA MACROS" group. >>>To unsubscribe from this group and stop receiving emails from it, send an >>>email >>>to excel-macros+unsubscr...@googlegroups.com. >>>To post to this group, send email to excel-macros@googlegroups.com. >>>Visit this group at http://groups.google.com/group/excel-macros?hl=en. >>>For more options, visit https://groups.google.com/groups/opt_out. >>> >>> >>> >>>-- >>>Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s >>>=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ >>>https://www.facebook.com/discussexcel >>> >>>FORUM RULES >>> >>>1) Use concise, accurate thread titles. Poor thread titles, like Please >>>Help, >>>Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not >>>get >>>quick attention or may not be answered. >>>2) Don't post a question in the thread of another member. >>>3) Don't post questions regarding breaking or bypassing any security measure. >>>4) Acknowledge the responses you receive, good or bad. >>>5) Jobs posting is not allowed. >>>6) Sharing copyrighted material and their links is not allowed. >>> >>>NOTE : Don't ever post confidential data in a workbook. Forum owners and >>>members >>>are not responsible for any loss. >>>--- >>>You received this message because you are subscribed to the Google Groups >>>"MS >>>EXCEL AND VBA MACROS" group. >>>To unsubscribe from this group and stop receiving emails from it, send an >>>email >>>to excel-macros+unsubscr...@googlegroups.com. >>>To post to this group, send email to excel-macros@googlegroups.com. >>>Visit this group at http://groups.google.com/group/excel-macros?hl=en. >>>For more options, visit https://groups.google.com/groups/opt_out. >>> >>> >>> >> >>-- >>Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s >>=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ >>https://www.facebook.com/discussexcel >> >>FORUM RULES >> >>1) Use concise, accurate thread titles. Poor thread titles, like Please Help, >>Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not >>get >>quick attention or may not be answered. >>2) Don't post a question in the thread of another member. >>3) Don't post questions regarding breaking or bypassing any security measure. >>4) Acknowledge the responses you receive, good or bad. >>5) Jobs posting is not allowed. >>6) Sharing copyrighted material and their links is not allowed. >> >>NOTE : Don't ever post confidential data in a workbook. Forum owners and >>members >>are not responsible for any loss. >>--- >>You received this message because you are subscribed to the Google Groups "MS >>EXCEL AND VBA MACROS" group. >>To unsubscribe from this group and stop receiving emails from it, send an >>email >>to excel-macros+unsubscr...@googlegroups.com. >>To post to this group, send email to excel-macros@googlegroups.com. >>Visit this group at http://groups.google.com/group/excel-macros?hl=en. >>For more options, visit https://groups.google.com/groups/opt_out. >> >> >> > -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
