Finding Number of Days

[seothtt 1]

If I have the following two columns in a table, is there a way to find the number of days?

 

Column 1                  |                  Column 2               |          Days

 

12/9/2012 6:56                     1/20/2013 1:47

11/7/2012 11:06                   1/19/2013 22:38

1/8/2013 1:21                       1/20/2013 1:30

 

I need to be able to do some computing on the actual number of days.

 

Thanks, 

Richard

[Legend 181]

First convert the date/time to seconds and then $eval subtract the results...

 

http://www.ubotstudio.com/forum/index.php?/topic/6646-howto-convert-date-time-to-seconds/ might help...

[odeesuba 24]

clear table(&Dates Table)
add list to table as row(&Dates Table, 0, 0, $list from text("12/9/2012 6:56,1/20/2013 1:47", ","))
add list to table as row(&Dates Table, 1, 0, $list from text("11/7/2012 11:06:00,1/19/2013 22:38:00", ","))
add list to table as row(&Dates Table, 2, 0, $list from text("1/8/2013 1:21,1/20/2013 1:30", ","))
set(#Current Row, 0, "Global")
loop($table total rows(&Dates Table)) {
    navigate("http://www.preceden.com/calculators/duration", "Wait")
    type text(<name="start_date">, $table cell(&Dates Table, #Current Row, 0), "Standard")
    type text(<name="end_date">, $table cell(&Dates Table, #Current Row, 1), "Standard")
    click(<name="commit">, "Left Click", "No")
    wait for element(<innertext="Results">, "", "Appear")
    set(#Days, $scrape attribute(<outerhtml=w"<td>* days</td>">, "innertext"), "Global")
    set table cell(&Dates Table, #Current Row, 2, #Days)
    increment(#Current Row)
    load html("Row {#Current Row}  is {#Days}")
    wait(5)
}
load html(&Dates Table)

 

 

Try the code above , if you need explanation , just ask

[Legend 181]

Then there's always something like this:

 

http://duaneflowers.com/recommends/days-between-dates.exe

 

[seothtt 1]

How do I strip out the Time?  The actual date format that I pull is "1/20/2013  7:34:00 AM"   How can I delete, remove, etc. the Time, so all I have left is "1/20/2013"

 

Richard

[VaultBoss 310]

Use REGEX... do a $replace regex expression with this REGEX string:

 

(\s*(\d{1,2}(:\d{2}){2}\s*(AM|PM)))

[seothtt 1]

Thanks VaultBoss

 

Richard

[UBotBuddy 331]

Here ya go.  Whipped this up today in between day job stuff.

 

clear table(&DateTBL)
create table from file("{$special folder("Application")}\\datedata.csv", &DateTBL)
clear list(%calendar)
add list to list(%calendar, $list from text("dummy
January
February
March
April
May
June
July
August
September
October
November
December", "
"), "Delete", "Global")
set(#tblIndex, "-1", "Global")
loop($table total rows(&DateTBL)) {
    increment(#tblIndex)
    set(#Col1Date, $eval("Date.parse(\"{$ConvertDate($table cell(&DateTBL, #tblIndex, 0))}\");"), "Global")
    set(#Col2Date, $eval("Date.parse(\"{$ConvertDate($table cell(&DateTBL, #tblIndex, 1))}\");"), "Global")
    set(#varDays, $eval($divide($divide($divide($divide($eval($subtract(#Col2Date, #Col1Date)), 1000), 60), 60), 24)), "Global")
    set table cell(&DateTBL, #tblIndex, 2, #varDays)
}
define $ConvertDate(#var0) {
    set(#varDate, #var0, "Global")
    set(#month, $replace regular expression(#varDate, "(\\d\{1,\}).(\\d\{1,\}).(\\d\{1,\})(.\{1,\})", "$1"), "Global")
    set(#day, $replace regular expression(#varDate, "(\\d\{1,\}).(\\d\{1,\}).(\\d\{1,\})(.\{1,\})", "$2"), "Global")
    set(#year, $replace regular expression(#varDate, "(\\d\{1,\}).(\\d\{1,\}).(\\d\{1,\})(.\{1,\})", "$3"), "Global")
    return("{$list item(%calendar, #month)} {#day}, {#year}")
}

 

Make sure your data is delimited by the "|" character.

12/9/2012 6:56|1/20/2013 1:47|
11/7/2012 11:06|1/19/2013 22:38|
1/8/2013 1:21|1/20/2013 1:30|

 

This code works like a champ!

[VaultBoss 310]

VaultBoss,

 

Say, you were trying to grab just the "day" portion from

 

Current Time Wednesday, January 23, 2013 at 12:14:25 AM EST

 

Then, what would the REGEX be ?

Same question if you tried extracting just the "month".

Same question if you tried extracting just the "date" (eg. just the "23" and not the remaining).

Same question if you tried extracting just the "year".

Same question if you tried extracting just the "time".

 

I decided to write a custom function to deal with your request (at least partially) which you

can download for free >>> on this thread <<< here.

 

The function actually contains a REGEX expression to replace unwanted characters from the string,

but mostly employs the TEXT to LIST functionality of UBot.

Might not be 100% what you wanted, and by all means, DO learn REGEX and find out how to use it

for your means, but it's still useful for you and anybody else on the forum.

 

Cheers!