Date and Time Issue - Formula Required

CDNWolf

Board Regular
Joined
Nov 14, 2010
Messages
62
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
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,059
Office Version
  1. 2013
Platform
  1. Windows
Mike,

If I understand correctly then maybe..

Excel Workbook
BC
603/10/201403:20 PM
703/10/201404:20 PM
803/10/201405:20 PM
903/10/201406:20 PM
1003/10/201407:20 PM
1103/10/201408:20 PM
1203/10/201409:20 PM
1303/10/201410:20 PM
1403/10/201411:20 PM
1504/10/201412:20 AM
1604/10/201401:20 AM
1704/10/201402:20 AM
1804/10/201403:20 AM
Sheet1


Hope that helps.
 

CDNWolf

Board Regular
Joined
Nov 14, 2010
Messages
62
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

Well-known Member
Joined
May 17, 2009
Messages
5,059
Office Version
  1. 2013
Platform
  1. Windows
Assuming that you have the dates to reference as column B (H for you?) then maybe...
Excel Workbook
ABC
6103/10/201403:20 PM
7 03/10/201404:20 PM
803/10/201405:20 PM
903/10/201406:20 PM
1003/10/201407:20 PM
1103/10/201408:20 PM
1203/10/201409:20 PM
1303/10/201410:20 PM
1403/10/201411:20 PM
15204/10/201412:20 AM
1604/10/201401:20 AM
1704/10/201402:20 AM
1804/10/201403:20 AM
Sheet1
 

CDNWolf

Board Regular
Joined
Nov 14, 2010
Messages
62

ADVERTISEMENT

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

Well-known Member
Joined
May 17, 2009
Messages
5,059
Office Version
  1. 2013
Platform
  1. Windows
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

Board Regular
Joined
Nov 14, 2010
Messages
62
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,910
Messages
5,525,583
Members
409,651
Latest member
Quasar Hunter

This Week's Hot Topics

Top