Ask Experts Questions for FREE Help !
Ask
    TitoTito's Avatar
    TitoTito Posts: 4, Reputation: 1
    New Member
    		  
    #1

    May 8, 2012, 11:36 AM
    Excel Date over Midnight
    Example of what I need:

    Cycle: 07:00 AM one day to 07:00 AM second day

    Date is 08.05.2012. (07:00 AM) (dd.mm.yyyy)

    For period between 08.05.2012 07:00AM to 09.05.2012 07:00AM

    In cell A1: 08.05.2012
    In cell A2: 09.05.2012


    When opening a document:

    In cell A1: Current Date
    In cell A2: Next Day Date
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
    		  
    #2

    May 9, 2012, 11:50 AM
    In cell A1: =TODAY() (formatted to display as you want)
    In cell A2: =TODAY() + 1 (formatted to display as you want)
    TitoTito's Avatar
    TitoTito Posts: 4, Reputation: 1
    New Member
    		  
    #3

    May 9, 2012, 12:24 PM
    That's not what I need. That's what I use now. The result of these values ​​is:
    If the date 08.05.2012. (07:00 AM) (dd.mm.yyyy), BEFORE MIDNIGHT 08.05/09.05

    In cell A1: 08.05.2012
    In cell A2: 09.05.2012

    AFTER MIDNIGHT 08.05/09.05

    In cell A1: 09.05.2012
    In cell A2: 10.05.2012

    I need:

    For period between 08.05.2012 07:00AM to 09.05.2012 07:00AM

    In cell A1: 08.05.2012
    In cell A2: 09.05.2012
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
    		  
    #4

    May 10, 2012, 07:39 AM
    I've read that 3 times and still don't see what you're saying differently. Time to show me.

    Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook. Make sure the workbook demonstrates your desired results if possible, or just highlight the cells you're trying to fix. Use BEFORE/AFTER sheets if that helps make it clearer.
    TitoTito's Avatar
    TitoTito Posts: 4, Reputation: 1
    New Member
    		  
    #5

    May 11, 2012, 06:43 AM
    See an example, please.
    Attached Files
  1. File Type: xls Date_over_Midnight.xls (71.5 KB, 125 views)
    2. JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
    		  
    #6

    May 11, 2012, 10:18 PM
    Formulas...

    A1: =IF(HOUR(NOW())<=6, TODAY()-1, TODAY())
    A2: =IF(HOUR(NOW())<=6, TODAY(), TODAY()+1)
    TitoTito's Avatar
    TitoTito Posts: 4, Reputation: 1
    New Member
    		  
    #7

    May 12, 2012, 12:23 AM
    It seems that this is it.

    Merci beaucoup
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
    		  
    #8

    May 12, 2012, 07:58 PM
    It just occurred to me that you don't need that fancy A2 formula, just use:

    =A1+1
Not your question? Ask your question View similar questions
 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

Excel: Convert numbers to date [ 4 Answers ]

I'm using excel 07 and I have a cell containing the number 11291997 which represents 11/29/1997. How can I convert this to a date format. The 11291197 is number formatted. Any suggestions would help, thanks!

Excel - transpose date number sequence [ 5 Answers ]

I have a number sequence that is back to front & want to represent this in the standard European format e.g.. 2009-08-24 should be 24/08/2009 There are several thousand date records to change How do I do this?

How to convert numbers to date in excel [ 2 Answers ]

As I am entering the date format dd/m/yyyy. The output is coming in the form of a number. For example.. as I am inputting the date as 10/8/2008, the output that I am getting is 39729. Can you please help me out in this?

Excel: date formula [ 5 Answers ]

Say you have a date (21/7/08) typed in a cell and want the cell directly below it to be automatically calculated 7 days from the date in the cell above (27/7/08). How do you do this? Then once you've done this, you want a column of dates to automatically appear as follows: 21/7/08 22/7/08...

Finding Final Date in Excel [ 2 Answers ]

Hi All, I am a new member in this community. I am working on a small project in Excel and I am stuck up before I start. My problem is: In my company employees are allowed to choose all the weekdays to work or they can work how many ever days they want in a week. So please imagine that an...

View more questions Search