Using the DateDiff function in VBA to get number of days between 2 dates -


so i'm trying 2 dates in excel sheet , use datediff function number of days between 2 dates. adding number of days , dividing the number of rows , average amount of days. far have total amount of days every row gets added , displayed on column "e" , number of rows placed on column "f". know close because @ 1 point worked dumb , changed , not. here code , excel sheet.

 sub getdays()      range("c1").select      until activecell.value = ""          date1 = datevalue(activecell.offset(1, 0).value)         date2 = datevalue(activecell.offset(1, 0).entirerow.cells(1, "d").value)          daycount = datediff("d", date1, date2) + daycount         activecell.offset(1, 0).entirerow.cells(1, "e").value = daycount          studentcount = studentcount + 1         activecell.offset(1, 0).entirerow.cells(1, "f").value = studentcount          activecell.offset(1, 0).select     loop   end sub!

here snippet of sheet

the issue discovered when testing code loop comparing activecell value determine when exit, code operating on cell below activecell, result of offset(1,0) call. when loop on last line of data, activecell.value = "3/25/2015 10:52", next line of code trying populate date1 datevalue of null since offset down 1 row. throws type mismatch error.

i've adjusted code below, works me:

sub getdays()    range("c1").select    until activecell.value = ""      date1 = datevalue(activecell.value)     date2 = datevalue(activecell.offset(0, 1).value)      daycount = datediff("d", date1, date2) + daycount     activecell.offset(0, 2).value = daycount      studentcount = studentcount + 1     activecell.offset(0, 3).value = studentcount      activecell.offset(1, 0).select   loop  end sub

i adjusted offset command looking @ same row @ times each loop. replaced "entirerow.cells(1, "d")" sections using column integer in offset().

you may need change second line to: range ("c2").select code work, depending on if data starts on row 1 or row 2.


-

Popular posts from this blog

html/hta mutiple file in audio player -

ok started coding .hta application , pretty html page .hta extension , adde mp3 player , did work <object data="test.mp3" type="audio/mp3" width="200" height="40"> <param name="src" value="test.mp3"> <param name="autostart" value="0"> </object> but it's 1 song playing again , again , plz there way add multiple files playlist believe it's should added "src" parameters not sure, i'm starting did add "," in between , , tried ";" none of them working i'm waiting answers :d i wanna know if can show me how become familiar tag see it's helpful , powerful activex, audio,video,pdf .... files i'm waiting impatiently [i don't know object, plz plz more clear] get inspired example hta_audio_player.hta <html> <head> <title>hackoo audio player playlist hackoo 2014</title> <hta:app...
Read more

debugging - Reference - What does this error mean in PHP? -

what this? this number of answers warnings, errors , notices might encounter while programming php , have no clue how fix. community wiki, invited participate in adding , maintaining list. why this? questions "headers sent" or "calling member of non-object" pop on stack overflow. root cause of questions same. answers questions typically repeat them , show op line change in his/her particular case. these answers not add value site because apply op's particular code. other users having same error can not read solution out of because localized. sad, because once understood root cause, fixing error trivial. hence, list tries explain solution in general way apply. what should here? if question has been marked duplicate of this, please find error message below , apply fix code. answers contain further links investigate in case shouldn't clear general answer alone. if want contribute, please add "favorite" error message, warning or notic...
Read more