## How to calculate the expected % of the task given the start date, the end date and TODAY ()? - task

### Microsoft Access DateDiff + Difference in time if end time is next day

```I have a table with records, where each record has a date column, then a start time column and end time column.
I am trying to do a datediff to get the duration in hours from start to end date with DateDiff('s',[Start Date[,[End Date])/3600.
This works perfectly for End dates that are on same day as date column, but sometimes the end date would be the next day like 12:45 AM. The date diff will give me a large negative number, how do I let it know its next day?
I dont own the data, so not much I can do with the table
Thanks!
```
```Try something like this:
```
```It can be done with pure math:
TotalHours = TimeValue(CDate([End Date] - [Start Date] + 1)) * 24```

### How to provide count of days between two dates in SAS

```I have several periods of time each year where day zero is a particular day. I need to create a list of all dates going forward and backward between the start and end of the period with their associated day number.
Here is an example:
Start Date (08APR2016)
Day Zero (01May2016)
End Date (04Aug2016)
The list would look like this:
Day Date
-37 08APR2016
-36 09APR2016
.
.
0 01MAY2016
1 02May2016
.
.
94 04AUG2016
I'm not sure how to write the SAS code necessary to do this and any assistance would be appreciated.
I'm currently doing it by creating an id variable with N which counts from start date to end date. For each list I then add the correct number to make the date I want zero. So in the example above for example I would do N -38 for the first one and then count up from there.
However, it gets messy when doing it for multiple lists and I was hoping there was a quick way to make a certain day zero and then count backwards to the first day and forwards to the last day.
```
```what you're asking is pretty straight forward. You should try some stuff and see what works and what doesn't.
data want;
retain day date;
keep day date;
format date date9.;
startDate = '08APR2016'd;
endDate = '04AUG2016'd;
zeroDate = '01MAY2016'd;
do i = startDate to endDate;
day = i - zeroDate;
date = i;
output;
end;
run;```

### How to add seconds up in database

```In my pageload I have a counter.
When it counts to 10, it has to add 10 seconds up in the database (update?),
after that the timer resets, when it reaches 10 seconds again, its has to add up another 10 seconds again in the database.
(so my database keeps track on how long I've been on my page)
What sql statement do I have to use for this?
```
```The syntax for the Microsoft Access DateAdd function is:
DateAdd ( interval, number, date )
Parameters or Arguments
interval is the time/date interval that you wish to add. It can be one of the following values:
Value Explanation
yyyy Year
q Quarter
m Month
y Day of the year
d Day
w Weekday
ww Week
h Hour
n Minute
s Second
number is the number of intervals that you wish to add.
date is the date to which the interval should be added.```

### Calculate a time used with start and end times on different days

```I am trying to calculate the amount of time an asset is being used for a given day. So for example if an asset is used from 1/1/2015 4:00 AM to 1/1/2015 2:00PM I can use a datediff function for the 2 times and get a difference of 10 hours meaning the the asset was used for 10 hours on the 1st. The problem occurs when the end time goes past midnight. In that situation I would need to know how many hours the asset was used till midnight. Is there an easy way to calculate this?
```
```You can use:
Dim HoursUsed As Integer
If DateDiff("d", StartTime, StopTime) > 0 Then
StopTime = DateValue(StopTime)
End If
HoursUsed = DateDiff("h", StartTime, StopTime)
For an exact measure of time use:
Dim TimeUsed As Date
TimeUsed = StopTime - StartTime
Then format TimeUsed as you like.```

### function which divides time period by periodicity

```I need help to creating a function that can divide a period of time into regular periodicity of year.
For example: I have a period from 11/10/2011 to 08/07/2012 divided on regular semester. I want to get this result in a list:
1- 11/10/2011 - 12/31/2011
2- 01/01/2012 - 06/30/2012
3- 07/01/2012 - 08/07/2012
```
```As you said, it is little bit complex but not impossible. With small thought you might get this easily. I've implemented code but to give your brain some work I'm not posting the code but giving you pseudo code below.
I thought of following way. First you need to have a duration through which you want to divide a year, for ex: 6 months.
Take the start date and create a new date by using DateTime constructor (year, month, day) and pass the year in the start date as year and month and day as 1 as below:
new DateTime(startdate.Year, 1, 1);
This will give you start date of that year. Then add duration days/months to that date to get the next periodicity. If your start date is less than this new date then add once again duration to that new date till you get the date greater than start date.
With the above logic you can form the periods you want till the end date. Of course you have to check whether your end date as well against this periodicity. There are many other conditions you need to check to get the proper system to give these periods for any given start and end dates.```