Excel and Microsoft Date Code Conversion -


how convert (69.36) representation of how many months, days, hours ,minutes & seconds?

update 2: sorry, there still few more cases code in update 1 won't match. have 2 suggestions, 1 easy days, hours, minutes, , seconds:

=floor(b4-a4,1)&":"&text((b4-a4)-floor(b4-a4,1),"hh:mm:ss")

but request above wants months tricky built in date functions because months not same length in days. before, have 2 cell solution ugly , long appears work in more cases:

store: in c1

=12*(year(b1)-year(a1))+(month(b1)-month(a1))+if(day(b1)-day(a1)+if(floor(b1,1)<floor(a1,1),-1,0)<0,-1,0)

final solution formula

=floor(c1/12,1)&":"&mod(c1,12)&":"&floor(b1-date(year(a1),month(a1)+c1,day(a1)),1)&":"&text((b1-a1)-floor(b1-a1,1),"hh:mm:sss")

results:

a               b               c           result      simple days + time formula 5/1/2006 0:00   5/2/2007 0:00   12  1:0:1:00:00:00      366:00:00:00 5/1/2006 10:35  5/2/2007 15:30  12  1:0:1:04:55:00      366:04:55:00 5/1/2007 10:35  5/2/2007 15:30  0   0:0:1:04:55:00      1:04:55:00 5/2/2007 10:35  5/2/2007 15:30  0   0:0:0:04:55:00      0:04:55:00 5/2/2006 10:35  5/2/2007 15:30  12  1:0:0:04:55:00      365:04:55:00 5/2/2005 10:35  5/2/2007 15:30  24  2:0:0:04:55:00      730:04:55:00 5/1/2006 18:30  5/2/2007 15:30  12  1:0:1:21:00:00      365:21:00:00 5/1/2006 17:35  5/2/2007 15:30  12  1:0:1:21:55:00      365:21:55:00 4/25/2007 17:35 5/2/2007 15:30  0   0:0:7:21:55:00      6:21:55:00 5/7/2006 18:30  5/2/2007 15:30  11  0:11:25:21:00:00    359:21:00:00

those last 2 rows failed deliver correct result in last update's formula.

update 1: there problem way old formula gave handles dates in different months. odd happens in counting months. no idea why. again consider 2 times in a1 (1/1/2006 10:35:00 am) , b1 (5/2/2007 3:30:00 pm). in c1 want following formula calculate date month , year b1 @ same time , day a1:

=date(year(b1),month(b1),day(a1))+time(hour(a1),minute(a1),second(a1))

then (much uglier) formula solve problem is:

=if(c1-a1>0,text(c1-a1,"yy:mm")&":"&text(b1-c1,"dd:hh:mm:ss"),"00:00:"&text(b1-a1,"dd:hh:mm:ss"))

i haven't testing exhaustively tested number of cases , seems work. 

a               b               c           result 5/1/2006 0:00   5/2/2007 0:00   5/1/2007    00:12:01:00:00:00 5/1/2006 10:35  5/2/2007 15:30  5/1/2007    00:12:01:04:55:00 5/1/2007 10:35  5/2/2007 15:30  5/1/2007    00:00:01:04:55:00 5/2/2007 10:35  5/2/2007 15:30  5/2/2007    00:00:00:04:55:00 5/2/2006 10:35  5/2/2007 15:30  5/2/2007    00:12:00:04:55:00 5/2/2005 10:35  5/2/2007 15:30  5/2/2007    01:12:00:04:55:00

nb: no error checking in case date in b greater a, in case gives "#value!" error.

old version a1 contains date 1 5/9/2007 10:35 , b1 contains date 2 6/9/2007 15:30 difference in time:

=text(b1-a1,"m:d:h:mm:ss")

which gives desired result

1:31:4:55:00

Comments

Post a Comment

Popular posts from this blog

javascript - Jquery show_hide, what to add in order to make the page scroll to the bottom of the hidden field once button is clicked -

i have button on website when clicked, reveals map. button can seen when page first loads up, when clicked, section holding map slides open , goes out of screen since page loads @ top (normal). want page scroll down bottom of div select when button pushed, if map out of screen. if page scrolled down, , clicks show map button, don't want page jump bottom of map. i tried figure out myself, no luck. seasoned jquery coders able give me guidance? many thanks! map = mapdiv.gmap3("get"); infobox = new infobox({ pixeloffset: new google.maps.size(-220, -310), closeboxurl: '', enableeventpropagation: true }); mapdiv.delegate('.infobox .close','click',function () { infobox.close(); }); jquery(".slidingdiv").hide(); jquery(".show_hide").show(); jquery('.show_hide').click(function(){ lastcenter=map.getcenter(); jquery(".slidingdiv&quo
Read more

javascript - Highcharts multi-color line -

Image
i looking create highchart multi-colored line. if goes down under value, become red, , when comes on value, become green . here picture example: or maybe highcharts have other charts models differentiate high or low level color? you can achieve coloring using lineargradient coloring: color: { lineargradient: { x1: 0, x2: 0, y1: 0, y2: 1 }, stops: [ [0, 'red'], [0.25, 'yellow'], [0.50, 'green'], [0.75, 'yellow'], [1, 'red'] ] } here 0 in stops relates minimum value of series , 1 maximum value, , in between percentages between values. if find percentages according values prior creating chart use correct stops values make relate specific values in data (at least approximately). see this jsfiddle example .
Read more

javascript - Enter key does not work in search box -

tried search guys sorry. input in search box doesnt work (or search) when hit enter when user clicks search button. doing wrong? here code. thanks. <script type="text/javascript"> $(document).ready(function() { $("#submit").click(function() { var url = "http://asla.org/awardssearch.html"; url += "?s=" + $('#googlecse').val(); window.location = url; }); $('#googlecse').keydown(function(event) { if (event.keycode == '13') { $("#submit").click(); } else { //do nothing :) } }); }); </script> <form class="navbar-form navbar-right" role="search"> <div class="search"> <input id="googlecse" type="text" onblur="if(this.value=='')this.value=this.defaultvalue;" onfocus="if(this.value==this.defaultvalue)t
Read more