#### Date and Time setup

```I work at a 24 hour job we take readings every hour. I want to have the date
autofill a cell as the current date but our sheet changes at 0700 every day.
So if the sheet is worked on at 0100 the date needs to be for the past day
starting at 0700, if the time for the current day changes to 0800 the cell
will display the new date. ie:
0100hrs = 4/18/06
0600hrs = 4/18/06
0800hrs = 4/19/06
etc...
```
 0
4/19/2006 5:39:10 AM
excel 39879 articles. 2 followers.

2 Replies
698 Views

Similar Articles

[PageSpeed] 31

```=TODAY()-(A1<=TIME(7,0,0))*1

HTH
--
AP

"Joe" <jdbarnes63@earthlink.net> a �crit dans le message de
> I work at a 24 hour job we take readings every hour. I want to have the
date
> autofill a cell as the current date but our sheet changes at 0700 every
day.
> So if the sheet is worked on at 0100 the date needs to be for the past day
> starting at 0700, if the time for the current day changes to 0800 the cell
> will display the new date. ie:
>  0100hrs = 4/18/06
>  0600hrs = 4/18/06
>  0800hrs = 4/19/06
> etc...

```
 0
ardus.petus (319)
4/19/2006 6:21:09 AM
```That looks good but does not work. Here is a better way that I need it
ti work..Joe

I need to have the current day autofill a cell but it needs to be based
on a time change at 0700 hrs. What I need is to be able to fill out my
form, have the current date display in the cell but when the system
clock changes to 0700 the date will roll up to the next day.

0100 hrs 4/18/06
0600 hrs 4/18/06
0900 hrs 4/19/06
2059 hrs 4/19/06
0100 hrs 4/19/06
0500 hrs 1/19/06
0705 hrs 1/20/06

--
JDBARNES63
------------------------------------------------------------------------
JDBARNES63's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=33631

```
 0
4/19/2006 6:33:40 AM

Similar Artilces:

actual time and date
Hello, Does anyone know the command for adding the actual time and date to a string? thx in advance -- kizzie ------------------------------------------------------------------------ kizzie's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26092 View this thread: http://www.excelforum.com/showthread.php?threadid=394484 How about ="String"&Text(Now(),"d/m/yyyy hh:mm") Rgds, ScottO "kizzie" <kizzie.1tje2f_1123657509.5167@excelforum-nospam.com> wrote in message news:kizzie.1tje2f_1123657509.5167@excelforum-nospam.com... ...

X axis date
Hi I'm using Excel '97 (Yeah, I know, but it's all I can afford at the moment) I want the X axis to always display a month beyond the latest date, so that the line graph stops short of the right hand side. I can get the Y Axis of value to stay ahead of the game by a consistent amount, but the date defaults back to the latest date when Auto for Max value is ticked. Is this possible? Will I have to use VBA? Please clue me in. Thanks Dave F. Add a series to the chart. It can consist of a single point, where the date is calculated as the maximum in your date column plus 30, an...

Is it posible to have a date/text field in an access db
Can I have a date field that is also a text field? In some instances a date is not required and it is important that it is specified as NA in that field. =?Utf-8?B?QWxiZXJ0?= <Albert@discussions.microsoft.com> wrote in news:FC4D71FD-5D6E-43D0-A5A4-DD7AD207D038@microsoft.com: > Can I have a date field that is also a text field? In some > instances a date is not required and it is important that it is > specified as NA in that field. > No. You can add a checkbox beside the date to store true or false associated with N/A, and use that to hide the textbox on a form or r...

time and curency
if i wanted to put 2 times in and get excel to work out the time take in hours between it and then times that by another cell which is th hour rate in � giving a out put in � how would i do it? i cant get excel to take the hours worked and convert it into mone earned. for example 4:55 hours and �5 hourly rate would be �24.58 but cant get excel to do this. please help thankyou Da Attachment filename: wages.xls Download attachment: http://www.excelforum.com/attachment.php?postid=62592 -- Message posted from http://www.ExcelForum.com Hi! If the earlier ...

Setup was unable to find instance of CRM services
This occours when I try to run the Sales for Outlook, CRM works fine on the same machine through IE. is your workstation and the crm server in the same domain? "Vignir" <anonymous@discussions.microsoft.com> wrote in message news:13a0c01c41ad7\$b0162a70\$a001280a@phx.gbl... > This occours when I try to run the Sales for Outlook, CRM > works fine on the same machine through IE. > ...

Calculating time between successful data transmissions
Hi - I need a little help devising something that will basically do the following: I have a set of dates of transmissions and I want to find the time between successful transmits. See below: 11/11/04 6:01 DIAL 1-800-827-1159 11/11/04 6:02 Nothing was received. 11/11/04 6:04 DIAL 1-800-827-1159 11/11/04 6:07 DIAL 1-800-827-1159 11/11/04 6:10 DIAL 1-800-827-1159 11/11/04 6:14 DIAL 1-800-827-1159 11/11/04 6:17 DIAL 1-800-827-1159 11/11/04 6:18 Nothing was received. 11/11/04 6:20 DIAL 1-800-827-1159 11/11/04 6:24 DIAL 1-800-827-1159 11/11/04 6:25 Nothing was received. 11/11/04 6:27 DIAL 1-800...

Project Time & Expense
We just upgraded to GP 9.0 and BP 3.0. My manager noticed that when she returned a timesheet in Project Time & Expense, it no longer allows her to supply a note. How do we get back that option? My guess... I had to apply the setting for 'AutomaticProcessing" to 0, described in Article ID# 921642. I noticed that besides "AutomaticProcessing" there are other fields in table "PmaTimesheetConfig". One is called "ApprovalNoteSubmit". Does this turn back on the approval rejection note? Can someone list what behavior each of these fields changes? Than...

email
The time recorded on all my incoming email is clearly and unequivocally incorrect, by several hours. I haven't yet checked to see if my outgoing mail is arriving at its assigned destination also with an incorrect time. My OS (Vista) date/clock is showing the correct values. Can anyone explain why this is happening and how I might resolve this issue? I thought I was undecisive but now I''m not too sure "Pompeygill" <Pompeygill@discussions.microsoft.com> wrote in message news:4795D99B-8C65-433A-94CA-2E9AAADA619D@microsoft.com... > The time recorded on all ...

create date from separate cells
I have: A1=9 B1=1 C1=08 D1=10 E1=13 F1=12 I want G1 to read: 9/1/08 10:13:12 using a formula Any help appreciated Thanks The problem is your year in C1, Excel would probably think it is 1908. Can there be no earlier years than 2000 you can use =DATE(2000+C1,A1,B1)+TIME(D1,E1,F1) then format the cell as m/d/yy h:mm:ss -- Regards, Peo Sjoblom "Flash in the Pan" <hotwire999@hotmail.com> wrote in message news:47445417-490a-46c1-8078-96fe4b14c75c@n38g2000prl.googlegroups.com... >I have: > > A1=9 > B1=1 > C1=08 > D1=10 > E1=13 > F1=12 > &g...

Convert Text to DATE #3
Hello all, I hope you can help with a problem we have in the office. We've got over 4,500 observations which are supposed to be in Date Format. Instead they are in text format, e.g. "01.06.2005 08:26" or just "09.03.2005" but we need to perform various duration calculations on them, so we need them in the Date Format I tried Format Cells-> Category = Date, but it doesn't work. It works however when I enter the cell and then click "Enter". very strange Please help if you have an idea how to do it... Thank you very much Try =DATEVALUE(SUBSTITU...

Help finding first payment date and amount
I have a simple table and I need help finding the first payment date and the amount payed. Here is the table CREATE TABLE [dbo].[Payments]( [MemberID] [nchar](10) NOT NULL, [PaymentDate] [datetime] NOT NULL, [Amount] [money] NULL, CONSTRAINT [PK_Payments] PRIMARY KEY CLUSTERED ( [MemberID] ASC, [PaymentDate] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] Here is some data to insert insert into payments values (101,'01-01-2009',20) insert into payment...

How time work with a file excel ?
Hello from Italy , i have an old five of excel (five years old) . I work with this every day , now i have the curiosity to know : how many hours it was open ? TIA Hi if I understood you correctly there is no chance to get this information :-) -- Regards Frank Kabel Frankfurt, Germany barbun wrote: > Hello from Italy , > i have an old five of excel (five years old) . > I work with this every day , now i have the curiosity to know : how > many hours it was open ? > > TIA Arbun; This is possible with a VBA macro, but I have to write that. Maybe google can serve you bett...

Sent Mail Dates Replaced with word NONE
I am using Outlook 97 on a W2k machine. I have suddenly developed a problem in the Sent Items folder. Several weeks back the sent date changed from the usual date to the word NONE. The other folders seem to be OK. What and where can I check settings to look for the root cause of this? TIA, Carl -- Wishing you a VERY HAPPY NEW YEAR!! May it far exceed your expectations. ...

julian date #3
Is there a formula or line of code that, in a date cell, would automatically convert a date entered in the 07/16/04 format into the 04188 julian format? thank you. You can't have a formula in a cell and make an entry in it - the formula is overwritten. You can use an event macro: Put this in the worksheet code module (right-click the worksheet tab and choose View Code): Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim nYear As Long Dim nDays As Long With Target If .Count > 1 Then Exit Sub If Not Intersect(.Cells, Ra...

Remove time from date upon export to csv
I cannot remove the time from the date when exporting invoice data to a csv file. I have followed previous advice and created a query based on my tblInvoices table, in which I have formatted the InvDate field in the Design Grid as follows: Date: Format([tblInvoices.InvDate], "dd/mm/yyyy") (UK format) In the Query, this is fine - just the date, no time shown. I saved an export spec in the Advanced bit of the File/Export wizard. But in the resulting csv, there are six zeroes representing the time! However if I do a manual export by following exactly the same process bu...

Entering Dates in Excel
Hi, Is it possible to just enter 020304 in a cell and have the date come out correctly or must I put -'s or \'s to seperate the numbers Hi John without using VBA this is (AFAIK) not possible. Have a look at http://www.cpearson.com/excel/DateTimeEntry.htm for a VBA solution Frank John wrote: > Hi, > > Is it possible to just enter 020304 in a cell and have > the date come out correctly or must I put -'s or \'s to > seperate the numbers ...

Opening calendar (month view), I want today's date on top row
I open and use calendar in the month view. if todays date is in the last week of the month, then it is in the bottom row. That means I see all of my appointments, etc. from the last month rather than those in my future. I would like the (uncompressed weekend) month view to open with today's date in the top row... is that possible? No, its not possible. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:dailytips-subscribe-request@lists.outlooktips....

How to change default date formula on Excel worksheet
Excel 2007 Want to change the default formula for date on worksheet. Currently any date input auto displays as "17-Apr" for example. I know how to format to display 4/17/2010, but want this as default when I open the worksheet. In excel 2003 it was easy set up in "Options" in 2007, I can't seem to figure how to set this default. Advises please Change it Windows CP, Regional & Language Options. -- Regards Dave Hawley www.ozgrid.com "alexanderoux" <alexanderoux@discussions.microsoft.com> wrote in message news:6E394AB6-C944-4...

Account Setup
I ma using Outlook 2002 sp3 in a ADS Domain environment. I used to use Outlook 2000. With 2000 I had the client login configured so that the userid and Domain name were pre populated when Outlook launched. can someone tell me where in the Registry that is specified for Outlook 2002? Thanks in advance for any help provided!! Wilbur "WIlbur" <anonymous@discussions.microsoft.com> wrote in message news:16dff01c448b3\$156d0a70\$a601280a@phx.gbl... > Thanks in advance for any help provided!! > Wilbur This is a complicated task that Outlook Profiler seems to have fixed. ...

view date navigator
has anyone found a way to view the "date navigator" while they are in their inbox? Nope. Can't be done. -- Patricia Cardoza Outlook MVP Author - Special Edition Using Microsoft Office Outlook 2003 Lead Author - Access 2003 VBA Programmer's Reference Author - Absolute Beginner's Guide to Microsoft OneNote 2003 http://blogs.officezealot.com/cardoza "lisa" <anonymous@discussions.microsoft.com> wrote in message news:2a6a01c4a8a1\$1b7d3d10\$a401280a@phx.gbl... > has anyone found a way to view the "date navigator" while > they are in their...

Display a range of dates in a calendar
How can I show a range of dates in a pop-up calendar ?? Let´s say I want to show the range between 2010-03-10 and 2010-04-02 with a different color than the other dates in the calendar. How to do ??? -- Thanks in advance for Your help Have a look at the Flex Grid Appointment-Bookings Demo at :- http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=21&SID=5debzz2c87c6957859f13b1d2zcccfb4 Peter Hibbs. On Sun, 14 Mar 2010 11:32:01 -0700, Leif Thorsen <LeifThorsen@discussions.microsoft.com> wrote: >How can I show a range of dates in a pop-up calenda...

SelStart Error
I have been using input masks for dates on forms. I usually add code to force the cursor to the beginning of the text box using SelStart. Private Sub txtDate1_GotFocus() Me.txtDate1.SelStart = 0 End Sub I created a form and the above field functioned fine. After I added more controls and code I began to get a Run-time 2185 error -You can't reference a property or method for a control unless the control has the focus. Anyone know what causes this or is my form corrupted in some way? Thanks in advance for any help or insight, -- Scott S Scott, the only case that comes to mind ...

excel office home and student freezes all the time
I purchased office home and student 2007 several months ago. I have Vista on my computer. I had issues with Word, but was able to fix it. I have had no reason to use Exce, until now. When ever I open it, it constantly freezes up, with the little circle spinning. It says all the updates are on my computer. I have no idea how to fix this issue, and it is very frustrating, and time consuming trying to fix this issue. Thanks for you help. Steve ...

Matching Dates
Ok guys fairly complicated one here... I have a single table with 20,000 transactions within it. Each transaction has a date attached to it. I have another table which contains 3 fields, a StartDate, a FinishDate and a WeekNumber. i.e.: startdate finishdate weeknumber 01/01/2007 08/01/2007 1 What I need to be able to do is take each transactions from the transactions table and link it to this calendar table to get the weeknumber of when each transaction occured. How on earth can I produce this? Many thanks. Ash. hi, ashg657 wrote: > Ok ...

ComboBox DropDown Size: Design vs Run Time?
Hi, I was wondering, what's the reason for specifying the dropdown size of a combobox at design time? I'd say it's better to make the size as large as possible to display as much rows (and columns) without falling of the screen. Olaf van der Spek wrote: > Hi, > > I was wondering, what's the reason for specifying the dropdown size of a > combobox at design time? > I'd say it's better to make the size as large as possible to display as > much rows (and columns) without falling of the screen. Sounds reasonable. Are you having some kind of issue wit...