time/date spreadsheet
Posted by mark on September 02, 2001 7:46 AM
How do I make a spreadsheet to do the following with the following fields?
1st field: date of occurance
2nd field: occurance completion time
3rd field: report completion date
4th field: report completion time
5th field: total hours and minutes to complete
basically, i'm trying to have the it find how long it took to complete a report from the date and time of the occurance. for example:
if the occurance happened at 11:55pm on the 09/02/01 and the report was completed on 09/03/01 (or any other date), how many hours and minutes did it take to complete?
please respond w/formulas and/or macros or whatever it takes. i need this asap. thanks in advanced. mark
Posted by Mark W. on September 02, 2001 8:07 AM
='report completion date'+'report completion time'-'date of occurance'+'occurance completion time'
Format as [h]:mm
Posted by mark on September 02, 2001 9:27 AM
where do i put the formula at and also where do i put the format of [h]:mm
Posted by Mark W. on September 02, 2001 2:24 PM
You format the cell containing this formula as
[h]:mm. Since you didn't provide cell references
where your dates were located I provided a formula
based on the field names that you provided. If you
so wish you may substititute cell references for
these field names; otherwise, you'll need to enter
this formula into any cell that not beneath these
headers.
Posted by mark on September 02, 2001 3:31 PM
sorry, here's the following:
1st field(E2): date of occurance
2nd field(G2): occurance completion time
3rd field(I2): report completion date
4th field(K2): report completion time
5th field(M2): total hours and minutes to complete.
date of occurance,occurance completion time,report completion date,
report completion time, total hours and minutes to completed are the names of the fields started on the top cell.
ex: E1=date of occurance,etc.
all data imput starts on row 2
thanks again for putting up with this beginner. mark
Posted by Mark W. on September 02, 2001 4:31 PM
Enter, =G2+H2-E2+F2, on row 2 in an unused column.
Posted by mark on September 02, 2001 5:12 PM
i entered the formula as below and didn't get an answer, am i missing something?
Posted by Mark W. on September 03, 2001 10:55 AM
Sorry, I goofed...
Use =I2+K2-E2+G2
Posted by MARK on September 03, 2001 6:41 PM
Re: Sorry, I goofed...
Tried what you did and it came back with #VALUE!
Any ideas, I used the following in these field for imput:
I2(Report Completion Date) 09/02/2001
K2(Report Completion Time) 3:00AM
E2(Date of Occurance) 09/01/2001
G2(Occurance Completion Time) 11:00PM
And instead of coming back with hours and minutes, it came back with #VALUE!
Do I need to format the cells or something or am I missing something?
Thanks, mark
Posted by Mark W. on September 04, 2001 7:23 AM
Check your date values...
Make sure that your entering numeric date values
and not text.
Posted by mark on September 04, 2001 3:28 PM
Re: Check your date values...
I'm entering the dates as: 09/01/2001 for the first date(E2) and
09/02/2001 for the second date(I2).
do I need to do something in cells by style formating?
Posted by Mark W. on September 05, 2001 6:54 AM
Re: Check your date values...
You've listed your time values as 3:00AM and 11:00PM.
This is not the proper way to enter a time. Enter
them with a space before AM and PM (e.g., 3:00 AM).
For more info on the entry of dates and times see
the Excel Help topic for "Tips on entering dates
and times".
