Hello,

I have a start date and time which is inputed to a form in the format of
##/##/#### HH:MM:SS

I also have a total time which is in decimal hours. Like 25.61.

How would I create an expression in a query to take the start date/time, add
the total time to it, and get the result in the date/time format.

If possible I would also like to exclude the hours between midnight and 5 AM.

Thanks in advance,
Emily

Emily

You are, to some extent, mixing apples and oranges -- both of your items are
somewhat related to dates, but not related directly to each other.

If you have a Date/Time value (forget the format, that's just how it gets
displayed, not how it is stored), you can use the DateAdd() function (see
Access HELP for syntax) to add/subtract days/time.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Emily" <(E-Mail Removed)> wrote in message
news:E917D2E1-9176-4080-825C-(E-Mail Removed)...
> Hello,
>
> I have a start date and time which is inputed to a form in the format of
> ##/##/#### HH:MM:SS
>
> I also have a total time which is in decimal hours. Like 25.61.
>
> How would I create an expression in a query to take the start date/time,
> add
> the total time to it, and get the result in the date/time format.
>
> If possible I would also like to exclude the hours between midnight and 5
> AM.
>
> Thanks in advance,
> Emily

[Start Date Time] + (INT([Decimal Hours]/24)) + (([Decimal Hours]-INT(Decimal
Hours])*0.04166)

Kind regards
Tim

"Emily" wrote:

> Hello,
>
> I have a start date and time which is inputed to a form in the format of
> ##/##/#### HH:MM:SS
>
> I also have a total time which is in decimal hours. Like 25.61.
>
> How would I create an expression in a query to take the start date/time, add
> the total time to it, and get the result in the date/time format.
>
> If possible I would also like to exclude the hours between midnight and 5 AM.
>
> Thanks in advance,
> Emily

Whoops, made a typo and missed a [ it should have read

[Start Date Time] + (INT([Decimal Hours]/24)) + (([Decimal
Hours]-INT([Decimal
Hours])*0.04166)


"Timbo" wrote:

> [Start Date Time] + (INT([Decimal Hours]/24)) + (([Decimal Hours]-INT(Decimal
> Hours])*0.04166)
>
> Kind regards
> Tim
>
> "Emily" wrote:
>
> > Hello,
> >
> > I have a start date and time which is inputed to a form in the format of
> > ##/##/#### HH:MM:SS
> >
> > I also have a total time which is in decimal hours. Like 25.61.
> >
> > How would I create an expression in a query to take the start date/time, add
> > the total time to it, and get the result in the date/time format.
> >
> > If possible I would also like to exclude the hours between midnight and 5 AM.
> >
> > Thanks in advance,
> > Emily

This is what I put in my query field:

Finish: [Start]+(Int([Manufacturing LT]/24))+([Manufacturing
LT]-Int(([Manufacturing LT])*0.04166))

I had to add in a couple parentheses in order for it to work, but now it
isn't coming up with the correct end date/time.

I will give you an example so maybe you can troubleshoot it:

Start Manufacturing LT Finish
9/18/2007 4:45:00 PM 7.40857068108902 9/26/2007 2:33:21 AM
9/10/2007 5:15:00 PM 9.57489203519821 9/20/2007 7:02:51 AM
9/1/2007 5:12:00 PM 13.58692284599 9/15/2007 7:17:10 AM
8/28/2007 8:41:00 AM 10.1934295596542 9/7/2007 1:19:32 PM

I believe it is counting my decimal hours as days. Can you fix the code?

Thanks,
Emily

"Timbo" wrote:

> Whoops, made a typo and missed a [ it should have read
>
> [Start Date Time] + (INT([Decimal Hours]/24)) + (([Decimal
> Hours]-INT([Decimal
> Hours])*0.04166)
>
>
> "Timbo" wrote:
>
> > [Start Date Time] + (INT([Decimal Hours]/24)) + (([Decimal Hours]-INT(Decimal
> > Hours])*0.04166)
> >
> > Kind regards
> > Tim
> >
> > "Emily" wrote:
> >
> > > Hello,
> > >
> > > I have a start date and time which is inputed to a form in the format of
> > > ##/##/#### HH:MM:SS
> > >
> > > I also have a total time which is in decimal hours. Like 25.61.
> > >
> > > How would I create an expression in a query to take the start date/time, add
> > > the total time to it, and get the result in the date/time format.
> > >
> > > If possible I would also like to exclude the hours between midnight and 5 AM.
> > >
> > > Thanks in advance,
> > > Emily

Oops, missed of the remaining hours less than a day. Try this

Finish: [Start]+(Int([Manufacturing LT]/24))+([Manufacturing
LT]-(Int([Manufacturing LT]/24)*24)*0.04166)

Sorry short of time, let me know if this works. I will check in the morning.
Kind regards
Tim

"Emily" wrote:

> This is what I put in my query field:
>
> Finish: [Start]+(Int([Manufacturing LT]/24))+([Manufacturing
> LT]-Int(([Manufacturing LT])*0.04166))
>
> I had to add in a couple parentheses in order for it to work, but now it
> isn't coming up with the correct end date/time.
>
> I will give you an example so maybe you can troubleshoot it:
>
> Start Manufacturing LT Finish
> 9/18/2007 4:45:00 PM 7.40857068108902 9/26/2007 2:33:21 AM
> 9/10/2007 5:15:00 PM 9.57489203519821 9/20/2007 7:02:51 AM
> 9/1/2007 5:12:00 PM 13.58692284599 9/15/2007 7:17:10 AM
> 8/28/2007 8:41:00 AM 10.1934295596542 9/7/2007 1:19:32 PM
>
> I believe it is counting my decimal hours as days. Can you fix the code?
>
> Thanks,
> Emily
>
> "Timbo" wrote:
>
> > Whoops, made a typo and missed a [ it should have read
> >
> > [Start Date Time] + (INT([Decimal Hours]/24)) + (([Decimal
> > Hours]-INT([Decimal
> > Hours])*0.04166)
> >
> >
> > "Timbo" wrote:
> >
> > > [Start Date Time] + (INT([Decimal Hours]/24)) + (([Decimal Hours]-INT(Decimal
> > > Hours])*0.04166)
> > >
> > > Kind regards
> > > Tim
> > >
> > > "Emily" wrote:
> > >
> > > > Hello,
> > > >
> > > > I have a start date and time which is inputed to a form in the format of
> > > > ##/##/#### HH:MM:SS
> > > >
> > > > I also have a total time which is in decimal hours. Like 25.61.
> > > >
> > > > How would I create an expression in a query to take the start date/time, add
> > > > the total time to it, and get the result in the date/time format.
> > > >
> > > > If possible I would also like to exclude the hours between midnight and 5 AM.
> > > >
> > > > Thanks in advance,
> > > > Emily

Tried that. Didn't work. Came up with this...

Start Manufacturing LT Finish
9/18/2007 4:45:00 PM 7.40857068108902 39351.1064873478
9/10/2007 5:15:00 PM 9.57489203519821 39345.2936420352
9/1/2007 5:12:00 PM 13.58692284599 39340.3035895127
8/28/2007 8:41:00 AM 10.1934295596542 39332.5552351152

Thanks for your patience.

"Timbo" wrote:

> Oops, missed of the remaining hours less than a day. Try this
>
> Finish: [Start]+(Int([Manufacturing LT]/24))+([Manufacturing
> LT]-(Int([Manufacturing LT]/24)*24)*0.04166)
>
> Sorry short of time, let me know if this works. I will check in the morning.
> Kind regards
> Tim
>
> "Emily" wrote:
>
> > This is what I put in my query field:
> >
> > Finish: [Start]+(Int([Manufacturing LT]/24))+([Manufacturing
> > LT]-Int(([Manufacturing LT])*0.04166))
> >
> > I had to add in a couple parentheses in order for it to work, but now it
> > isn't coming up with the correct end date/time.
> >
> > I will give you an example so maybe you can troubleshoot it:
> >
> > Start Manufacturing LT Finish
> > 9/18/2007 4:45:00 PM 7.40857068108902 9/26/2007 2:33:21 AM
> > 9/10/2007 5:15:00 PM 9.57489203519821 9/20/2007 7:02:51 AM
> > 9/1/2007 5:12:00 PM 13.58692284599 9/15/2007 7:17:10 AM
> > 8/28/2007 8:41:00 AM 10.1934295596542 9/7/2007 1:19:32 PM
> >
> > I believe it is counting my decimal hours as days. Can you fix the code?
> >
> > Thanks,
> > Emily
> >
> > "Timbo" wrote:
> >
> > > Whoops, made a typo and missed a [ it should have read
> > >
> > > [Start Date Time] + (INT([Decimal Hours]/24)) + (([Decimal
> > > Hours]-INT([Decimal
> > > Hours])*0.04166)
> > >
> > >
> > > "Timbo" wrote:
> > >
> > > > [Start Date Time] + (INT([Decimal Hours]/24)) + (([Decimal Hours]-INT(Decimal
> > > > Hours])*0.04166)
> > > >
> > > > Kind regards
> > > > Tim
> > > >
> > > > "Emily" wrote:
> > > >
> > > > > Hello,
> > > > >
> > > > > I have a start date and time which is inputed to a form in the format of
> > > > > ##/##/#### HH:MM:SS
> > > > >
> > > > > I also have a total time which is in decimal hours. Like 25.61.
> > > > >
> > > > > How would I create an expression in a query to take the start date/time, add
> > > > > the total time to it, and get the result in the date/time format.
> > > > >
> > > > > If possible I would also like to exclude the hours between midnight and 5 AM.
> > > > >
> > > > > Thanks in advance,
> > > > > Emily

Lets simplify things a bit:

Finish: [Start]+([Manufacturing LT]/24)

Be sure to apply the desired Date format to Finish.

HTH,



"Emily" <(E-Mail Removed)> wrote in message
news:25739D82-A81E-4BB3-AC73-(E-Mail Removed)...
> Tried that. Didn't work. Came up with this...
>
> Start Manufacturing LT Finish
> 9/18/2007 4:45:00 PM 7.40857068108902 39351.1064873478
> 9/10/2007 5:15:00 PM 9.57489203519821 39345.2936420352
> 9/1/2007 5:12:00 PM 13.58692284599 39340.3035895127
> 8/28/2007 8:41:00 AM 10.1934295596542 39332.5552351152
>
> Thanks for your patience.
>
> "Timbo" wrote:
>
>> Oops, missed of the remaining hours less than a day. Try this
>>
>> Finish: [Start]+(Int([Manufacturing LT]/24))+([Manufacturing
>> LT]-(Int([Manufacturing LT]/24)*24)*0.04166)
>>
>> Sorry short of time, let me know if this works. I will check in the
>> morning.
>> Kind regards
>> Tim
>>
>> "Emily" wrote:
>>
>> > This is what I put in my query field:
>> >
>> > Finish: [Start]+(Int([Manufacturing LT]/24))+([Manufacturing
>> > LT]-Int(([Manufacturing LT])*0.04166))
>> >
>> > I had to add in a couple parentheses in order for it to work, but now
>> > it
>> > isn't coming up with the correct end date/time.
>> >
>> > I will give you an example so maybe you can troubleshoot it:
>> >
>> > Start Manufacturing LT Finish
>> > 9/18/2007 4:45:00 PM 7.40857068108902 9/26/2007 2:33:21 AM
>> > 9/10/2007 5:15:00 PM 9.57489203519821 9/20/2007 7:02:51 AM
>> > 9/1/2007 5:12:00 PM 13.58692284599 9/15/2007 7:17:10 AM
>> > 8/28/2007 8:41:00 AM 10.1934295596542 9/7/2007 1:19:32 PM
>> >
>> > I believe it is counting my decimal hours as days. Can you fix the
>> > code?
>> >
>> > Thanks,
>> > Emily
>> >
>> > "Timbo" wrote:
>> >
>> > > Whoops, made a typo and missed a [ it should have read
>> > >
>> > > [Start Date Time] + (INT([Decimal Hours]/24)) + (([Decimal
>> > > Hours]-INT([Decimal
>> > > Hours])*0.04166)
>> > >
>> > >
>> > > "Timbo" wrote:
>> > >
>> > > > [Start Date Time] + (INT([Decimal Hours]/24)) + (([Decimal
>> > > > Hours]-INT(Decimal
>> > > > Hours])*0.04166)
>> > > >
>> > > > Kind regards
>> > > > Tim
>> > > >
>> > > > "Emily" wrote:
>> > > >
>> > > > > Hello,
>> > > > >
>> > > > > I have a start date and time which is inputed to a form in the
>> > > > > format of
>> > > > > ##/##/#### HH:MM:SS
>> > > > >
>> > > > > I also have a total time which is in decimal hours. Like 25.61.
>> > > > >
>> > > > > How would I create an expression in a query to take the start
>> > > > > date/time, add
>> > > > > the total time to it, and get the result in the date/time format.
>> > > > >
>> > > > > If possible I would also like to exclude the hours between
>> > > > > midnight and 5 AM.
>> > > > >
>> > > > > Thanks in advance,
>> > > > > Emily

Yeah that will work too. I don't know where my mind was going last night!

If you want to round it down to the last whole minute then you can use this:

Result: 0.000694444*Int([Finish Time]/0.000694444)

Dates and Times are stored as Numbers. Each integer represents a day, each
decimal represents a fraction of a day. To work out these fractions:

1 Day = 1
1 Hour = 1/24 = 0.041666666
1 Minute = 1/24/60 = 0.000694444
1 Second = 1/24/60/60 = 0.000011574

Each system we work with, be it Excel, Visual Basic, SQL Server has a
different Zero Date or Reference Date. To find out what it is, Format 0
(Zero) as a date. Then format Today's date as a number. You can see the
number represents the number of days, hours and minutes etc. that have passed
since the reference date.

Kind regards
Tim

"George Nicholson" wrote:

> Lets simplify things a bit:
>
> Finish: [Start]+([Manufacturing LT]/24)
>
> Be sure to apply the desired Date format to Finish.
>
> HTH,
>
>
>
> "Emily" <(E-Mail Removed)> wrote in message
> news:25739D82-A81E-4BB3-AC73-(E-Mail Removed)...
> > Tried that. Didn't work. Came up with this...
> >
> > Start Manufacturing LT Finish
> > 9/18/2007 4:45:00 PM 7.40857068108902 39351.1064873478
> > 9/10/2007 5:15:00 PM 9.57489203519821 39345.2936420352
> > 9/1/2007 5:12:00 PM 13.58692284599 39340.3035895127
> > 8/28/2007 8:41:00 AM 10.1934295596542 39332.5552351152
> >
> > Thanks for your patience.
> >
> > "Timbo" wrote:
> >
> >> Oops, missed of the remaining hours less than a day. Try this
> >>
> >> Finish: [Start]+(Int([Manufacturing LT]/24))+([Manufacturing
> >> LT]-(Int([Manufacturing LT]/24)*24)*0.04166)
> >>
> >> Sorry short of time, let me know if this works. I will check in the
> >> morning.
> >> Kind regards
> >> Tim
> >>
> >> "Emily" wrote:
> >>
> >> > This is what I put in my query field:
> >> >
> >> > Finish: [Start]+(Int([Manufacturing LT]/24))+([Manufacturing
> >> > LT]-Int(([Manufacturing LT])*0.04166))
> >> >
> >> > I had to add in a couple parentheses in order for it to work, but now
> >> > it
> >> > isn't coming up with the correct end date/time.
> >> >
> >> > I will give you an example so maybe you can troubleshoot it:
> >> >
> >> > Start Manufacturing LT Finish
> >> > 9/18/2007 4:45:00 PM 7.40857068108902 9/26/2007 2:33:21 AM
> >> > 9/10/2007 5:15:00 PM 9.57489203519821 9/20/2007 7:02:51 AM
> >> > 9/1/2007 5:12:00 PM 13.58692284599 9/15/2007 7:17:10 AM
> >> > 8/28/2007 8:41:00 AM 10.1934295596542 9/7/2007 1:19:32 PM
> >> >
> >> > I believe it is counting my decimal hours as days. Can you fix the
> >> > code?
> >> >
> >> > Thanks,
> >> > Emily
> >> >
> >> > "Timbo" wrote:
> >> >
> >> > > Whoops, made a typo and missed a [ it should have read
> >> > >
> >> > > [Start Date Time] + (INT([Decimal Hours]/24)) + (([Decimal
> >> > > Hours]-INT([Decimal
> >> > > Hours])*0.04166)
> >> > >
> >> > >
> >> > > "Timbo" wrote:
> >> > >
> >> > > > [Start Date Time] + (INT([Decimal Hours]/24)) + (([Decimal
> >> > > > Hours]-INT(Decimal
> >> > > > Hours])*0.04166)
> >> > > >
> >> > > > Kind regards
> >> > > > Tim
> >> > > >
> >> > > > "Emily" wrote:
> >> > > >
> >> > > > > Hello,
> >> > > > >
> >> > > > > I have a start date and time which is inputed to a form in the
> >> > > > > format of
> >> > > > > ##/##/#### HH:MM:SS
> >> > > > >
> >> > > > > I also have a total time which is in decimal hours. Like 25.61.
> >> > > > >
> >> > > > > How would I create an expression in a query to take the start
> >> > > > > date/time, add
> >> > > > > the total time to it, and get the result in the date/time format.
> >> > > > >
> >> > > > > If possible I would also like to exclude the hours between
> >> > > > > midnight and 5 AM.
> >> > > > >
> >> > > > > Thanks in advance,
> >> > > > > Emily
>
>
>

"If possible I would also like to exclude the hours between midnight and 5 AM."

I tried George's code and it worked fine. My only issue is how to exclude
the 5 hours between 12 and 5. For example: one start time could be 9/1/2007
5:12 PM
Then you add 13.59 hours and the finish would be 9/2/2007 6:47:13 AM. When
really it would be 5 hours later because of the hours when no one is working.

And you can't just subtract the hours from all of the calculations because
some of them occur within the 19 hours of the day.

Any ideas?

Thanks.
Emily



"Timbo" wrote:

> Yeah that will work too. I don't know where my mind was going last night!
>
> If you want to round it down to the last whole minute then you can use this:
>
> Result: 0.000694444*Int([Finish Time]/0.000694444)
>
> Dates and Times are stored as Numbers. Each integer represents a day, each
> decimal represents a fraction of a day. To work out these fractions:
>
> 1 Day = 1
> 1 Hour = 1/24 = 0.041666666
> 1 Minute = 1/24/60 = 0.000694444
> 1 Second = 1/24/60/60 = 0.000011574
>
> Each system we work with, be it Excel, Visual Basic, SQL Server has a
> different Zero Date or Reference Date. To find out what it is, Format 0
> (Zero) as a date. Then format Today's date as a number. You can see the
> number represents the number of days, hours and minutes etc. that have passed
> since the reference date.
>
> Kind regards
> Tim
>
> "George Nicholson" wrote:
>
> > Lets simplify things a bit:
> >
> > Finish: [Start]+([Manufacturing LT]/24)
> >
> > Be sure to apply the desired Date format to Finish.
> >
> > HTH,
> >
> >
> >
> > "Emily" <(E-Mail Removed)> wrote in message
> > news:25739D82-A81E-4BB3-AC73-(E-Mail Removed)...
> > > Tried that. Didn't work. Came up with this...
> > >
> > > Start Manufacturing LT Finish
> > > 9/18/2007 4:45:00 PM 7.40857068108902 39351.1064873478
> > > 9/10/2007 5:15:00 PM 9.57489203519821 39345.2936420352
> > > 9/1/2007 5:12:00 PM 13.58692284599 39340.3035895127
> > > 8/28/2007 8:41:00 AM 10.1934295596542 39332.5552351152
> > >
> > > Thanks for your patience.
> > >
> > > "Timbo" wrote:
> > >
> > >> Oops, missed of the remaining hours less than a day. Try this
> > >>
> > >> Finish: [Start]+(Int([Manufacturing LT]/24))+([Manufacturing
> > >> LT]-(Int([Manufacturing LT]/24)*24)*0.04166)
> > >>
> > >> Sorry short of time, let me know if this works. I will check in the
> > >> morning.
> > >> Kind regards
> > >> Tim
> > >>
> > >> "Emily" wrote:
> > >>
> > >> > This is what I put in my query field:
> > >> >
> > >> > Finish: [Start]+(Int([Manufacturing LT]/24))+([Manufacturing
> > >> > LT]-Int(([Manufacturing LT])*0.04166))
> > >> >
> > >> > I had to add in a couple parentheses in order for it to work, but now
> > >> > it
> > >> > isn't coming up with the correct end date/time.
> > >> >
> > >> > I will give you an example so maybe you can troubleshoot it:
> > >> >
> > >> > Start Manufacturing LT Finish
> > >> > 9/18/2007 4:45:00 PM 7.40857068108902 9/26/2007 2:33:21 AM
> > >> > 9/10/2007 5:15:00 PM 9.57489203519821 9/20/2007 7:02:51 AM
> > >> > 9/1/2007 5:12:00 PM 13.58692284599 9/15/2007 7:17:10 AM
> > >> > 8/28/2007 8:41:00 AM 10.1934295596542 9/7/2007 1:19:32 PM
> > >> >
> > >> > I believe it is counting my decimal hours as days. Can you fix the
> > >> > code?
> > >> >
> > >> > Thanks,
> > >> > Emily
> > >> >
> > >> > "Timbo" wrote:
> > >> >
> > >> > > Whoops, made a typo and missed a [ it should have read
> > >> > >
> > >> > > [Start Date Time] + (INT([Decimal Hours]/24)) + (([Decimal
> > >> > > Hours]-INT([Decimal
> > >> > > Hours])*0.04166)
> > >> > >
> > >> > >
> > >> > > "Timbo" wrote:
> > >> > >
> > >> > > > [Start Date Time] + (INT([Decimal Hours]/24)) + (([Decimal
> > >> > > > Hours]-INT(Decimal
> > >> > > > Hours])*0.04166)
> > >> > > >
> > >> > > > Kind regards
> > >> > > > Tim
> > >> > > >
> > >> > > > "Emily" wrote:
> > >> > > >
> > >> > > > > Hello,
> > >> > > > >
> > >> > > > > I have a start date and time which is inputed to a form in the
> > >> > > > > format of
> > >> > > > > ##/##/#### HH:MM:SS
> > >> > > > >
> > >> > > > > I also have a total time which is in decimal hours. Like 25.61.
> > >> > > > >
> > >> > > > > How would I create an expression in a query to take the start
> > >> > > > > date/time, add
> > >> > > > > the total time to it, and get the result in the date/time format.
> > >> > > > >
> > >> > > > > If possible I would also like to exclude the hours between
> > >> > > > > midnight and 5 AM.
> > >> > > > >
> > >> > > > > Thanks in advance,
> > >> > > > > Emily
> >
> >
> >