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
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