Time Difference -helppppp please!

[dorionsmom via AccessMonster.com]

I have a flight schedule with departure times in short time, i.e. 13:50.

In order to service the aircraft on time certain processes needed to be
completed at 45 mins out, 1 hour out, and 2 hours out.

How can I create a field in a query that will generate those respective times.
..
For example: A flight leaving at 13:50 needs to show the KRT (Kitchen Ready
Time) as 13:05 because 45 mins out is the designated KRT for all flights
regardless of what the departure time is?

ETD = Depature Time (short time)
KRT = Kitchen Ready Time (short time) (45 min out)
CRT = Cooler Ready time (short time) (1 hour our)

Thanks to anyone can help...I've tried almost every time/date function but I
can't figure it out

 

[Jeff Boyce]

You may be confusing what is shown (i.e., formatting) with what is stored.

What is the underlying data type in the table?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[bismuth83]

How about using the DateAdd() function? DateAdd("n",-45,now) would
give the date/time 45 minutes prior to now. So your formulas can be:

ETD = Depature Time (short time)
KRT = DateAdd("n",-45,[ETD]) (45 min out)
CRT = DateAdd("n",-60,[ETD]) (1 hour out)

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

TimeValue(DateAdd("n", -45, "13:50")) = 13:05

I added the TimeValue() function to avoid this:

DateAdd("n",-45, "00:30") = 12/29/1899 11:45:00 PM

"n" = minutes. See Access VBA Help article on DateAdd Function for more
info (hint: Open Immediate Window (Ctrl-G) type DateAdd and leave the
cursor on the word and hit the F1 key to get the Help article).

To get 24-hour format use the Format() function around the whole thing.

Format(TimeValue(DateAdd("n", -45, "13:50")),"hh:nn")

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSXezpIechKqOuFEgEQJQkwCghZLQZrvTqJISGdW8TFvxVfDE39sAoLyU
lEHjeswgvE4H3i9lLFb9ZaCx
=q2ll
-----END PGP SIGNATURE-----

 

[dorionsmom via AccessMonster.com]

Wow...thank you so much it worked like a charm...i just added a TimeValue and
Format function so that it would look the way I needed it...

Listen I have one more issue...
I have a macro that uploads some data from an Excel spreadsheet into a table.
The data type in excel is short time.

When those respective fields move over the times don't show if the data type
is short time ??
But when I change it to text a decimal will populate...i.e. .20625

Is is possible to covert this decimal into a time value??

How about using the DateAdd() function? DateAdd("n",-45,now) would
give the date/time 45 minutes prior to now. So your formulas can be:

ETD = Depature Time (short time)
KRT = DateAdd("n",-45,[ETD]) (45 min out)
CRT = DateAdd("n",-60,[ETD]) (1 hour out)

 

[dorionsmom via AccessMonster.com]

Thanks sooo much it worked like a charm...

TimeValue(DateAdd("n", -45, "13:50")) = 13:05

I added the TimeValue() function to avoid this:

DateAdd("n",-45, "00:30") = 12/29/1899 11:45:00 PM

"n" = minutes. See Access VBA Help article on DateAdd Function for more
info (hint: Open Immediate Window (Ctrl-G) type DateAdd and leave the
cursor on the word and hit the F1 key to get the Help article).

To get 24-hour format use the Format() function around the whole thing.

Format(TimeValue(DateAdd("n", -45, "13:50")),"hh:nn")

I have a flight schedule with departure times in short time, i.e. 13:50.

[quoted text clipped - 13 lines]

Thanks to anyone can help...I've tried almost every time/date function but I
can't figure it out

 

[dorionsmom via AccessMonster.com]

Thanks for replying...the other responses solved the issue..

Listen I have one more issue...
I have a macro that uploads some data from an Excel spreadsheet into a table.
The data type in excel is short time.

When those respective fields move over the times don't show if the data type
is short time ??
But when I change it to text a decimal will populate...i.e. .20625

Is is possible to covert this decimal into a time value??

You may be confusing what is shown (i.e., formatting) with what is stored.

What is the underlying data type in the table?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a flight schedule with departure times in short time, i.e. 13:50.

[quoted text clipped - 16 lines]

I
can't figure it out