Date and Time Issue - Formula Required

[CDNWolf]

Hi guys,

I'm editing an existing excel file that has a schedule and I'm having trouble writing a formula to;

1) enter the correct date based on the time being past midnight
2) enter the working day number to correspond with the date change as above ie: Day 1, 2, 3 of the job not actual date

The job start date is entered manually and is represented as 18-Sep-14 and the time is also entered manually and is displayed as 3:20 PM I thought if I combined the items and I pulled them down the screen the date and time would change automatically...

The Time needs to increased every hour and the formula is =C6+(1/24) no problem there. C6 is the original start time.
The Date needs to increase every time the time goes past midnight not sure how to write this formula...

There are a few existing formulas in the spreadsheet that sort of work but they do not work properly...
=IF(INT((C7-INT(C7))*10000)=416,H7,"") C7 is the next hour 4:20 PM (following the original 3:20pm) and H7 is the next date based on a formula of =H6+1/24 which does not work because based on the formula the date will only change at a 24 hour period. So at 3:20 PM the date will change to 19-Sep-14, I need it to change at midnight...

Any help would be appreciated.


Regards,
Mike

 

[Snakehips]

Mike,

If I understand correctly then maybe..

Excel Workbook
	B	C
6	03/10/2014	03:20 PM
7	03/10/2014	04:20 PM
8	03/10/2014	05:20 PM
9	03/10/2014	06:20 PM
10	03/10/2014	07:20 PM
11	03/10/2014	08:20 PM
12	03/10/2014	09:20 PM
13	03/10/2014	10:20 PM
14	03/10/2014	11:20 PM
15	04/10/2014	12:20 AM
16	04/10/2014	01:20 AM
17	04/10/2014	02:20 AM
18	04/10/2014	03:20 AM
Sheet1

Hope that helps.

 

[CDNWolf]

Tony,

Thanks so much, it works well...

Now can I pick you brain further?

IF A6=1 (1st day of the job) I would like A15=2 and on and on till the job finishes.

Any thoughts ?

 

[Snakehips]

Assuming that you have the dates to reference as column B (H for you?) then maybe...

Excel Workbook
	A	B	C
6	1	03/10/2014	03:20 PM
7		03/10/2014	04:20 PM
8		03/10/2014	05:20 PM
9		03/10/2014	06:20 PM
10		03/10/2014	07:20 PM
11		03/10/2014	08:20 PM
12		03/10/2014	09:20 PM
13		03/10/2014	10:20 PM
14		03/10/2014	11:20 PM
15	2	04/10/2014	12:20 AM
16		04/10/2014	01:20 AM
17		04/10/2014	02:20 AM
18		04/10/2014	03:20 AM
Sheet1

 

[CDNWolf]

You're a legend, something so simple was so hard to figure out.

I was using a formula if they were the same then leave blank otherwise add 1 but it wasn't working...

Thanks again

 

[Snakehips]

You are welcome.
Maybe flat whites are on you next time I'm in Sydney?

Also, I'm curious to know which end of the leg?

 

[CDNWolf]

I'll shout you a coffee, it's the least I could do

Not sure about the which end of the leg though...


Cheers,
Mike